How do I convert a MySQL query with LIMIT to a SQL Server query?
SELECT *
FROM tableEating
WHERE person = '$identity'
LIMIT 1;
Add a comment
|
3 Answers
LIMIT does not work in T-SQL. Use TOP instead:
SELECT TOP(1) * FROM tableEating WHERE person = '$identity';
As Aaron says, you also need an ORDER BY if you don't want to get an arbitrary row.
1 Comment
Aaron Bertrand
Just note that without an
ORDER BY you're going to get an arbitrary row, and this can change from execution to execution...LIMIT does not work and TOP(1) may also not work in nested statements.
So the right approach is to use OFFSET... FETCH NEXT:
SELECT * FROM TableName
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
That basically tells TSQL to take one row (NEXT 1 ROWS ONLY) starting from the first one (OFFSET 0).
2 Comments
AnOccasionalCashew
Why do you say that
Top(1) might not work in a nested statement? I'm curious about this, would you mind elaborating?Not sure, but I see that it's being discussed here too stackoverflow.com/questions/29358682/…
Take a look at the Microsoft page for 2008, later versions work the same way. If you like to do it dynamic use:
DECLARE @xCount INT = 20
SELECT TOP(@xCount) * FROM ...
