Limit in SQL query not working

Hello,

For some reason the command

exec(conn, 'SELECT * from "CATPROD"."dbo"."machLiebherr1" LIMIT 10')

or

exec(conn,'SELECT * from "CATPROD"."dbo"."machLiebherr1" order by idx DESC LIMIT 10')

returns an error

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '10'

I have similar problems with using TOP in an SQL script.

From what I know, the syntax is correct and is also recommended in an answer to the question 'How do I efficiently make a large SQL query into Matlab?' on this forum. Could someone please let me know how to fix the error?

This command works fine, so it is definitely something to do with the 'Limit'

exec(conn, 'SELECT * from "CATPROD"."dbo"."machLiebherr1" order by idx DESC')

Thanks!

Answer 1

[Brendan Hamm]

LIMIT is not available in SQL Server. There is a similar command TOP which you can use. Your other option is to set your database preferences for fetching in batches within MATLAB.

[George Mathai]

Thanks, Brendan! I tried TOP.

This code does not work

curs2 = exec(conn, 'SELECT TOP 10 * from "civet"."public".prgm_part_xref','cursorType','scrollable')

The error is

ERROR: syntax error at or near "10"; Error while executing the query

I get the same error with this code

curs2 = exec(conn, 'SELECT TOP 10 * from "civet"."public".prgm_part_xref order by id DESC','cursorType','scrollable')

and this code

curs2 = exec(conn, 'SELECT TOP 10 * from "civet"."public"."prgm_part_xref" order by id DESC','cursorType','scrollable')

This code works exec(conn, 'SELECT * from "civet"."public".prgm_part_xref','cursorType','scrollable')

The database is a postgres database. Not sure if that makes a difference.

Thanks!

[Brendan Hamm]

There were plenty of other suggestions on that forum which you may want to try. Without postgres on my machine I cannot try and replicate this behavior. I do point out that I thought this was SQL Server as you received the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '10'

This may indicate that you need a different driver for this connection if you wish to use these other sql queries. I could not say for sure if this is an issue.

Another option you have here is to use the Database Datastore which will allow you to query in batches and then take 10 elements in order by using the Map Reduce functionality.

[Brendan Hamm]

Another thing you may want to try is to use the JDBC driver. I know there are some query limitations with ODBC, so this may also solve your problem. I would actually try this first as it is by far the easiest of suggestions here.