Limit in SQL query not working

Question

George Mathai 2016-4-4

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/277111-limit-in-sql-query-not-working

评论： Brendan Hamm 2016-4-6

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!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Brendan Hamm 2016-4-4

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/277111-limit-in-sql-query-not-working#answer_216407

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.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Brendan Hamm 2016-4-6

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 2016-4-6

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.

请先登录，再进行评论。