How to write a (MySQL) "LIMIT" in SQL Server?

Question

I have a problem when i try to change a query with LIMIT from MYSQL to SQL-Server.

Check that :

SELECT * 
FROM tableEating 
WHERE person = '$identity' 
LIMIT 1;

I tried to change it with some queries but nothing work.

score 13 · Accepted Answer · edited Oct 08 '16 at 23:18

13

LIMIT does not work in T-SQL.

You have to use TOP instead, like this:

SELECT TOP(1) * FROM tableEating WHERE person='$identity';

I hope that will work for you.

As Aaron says, you also need an ORDER BY if you don't want to get an arbitrary row.

edited Oct 08 '16 at 23:18

doppelgreener

answered May 03 '12 at 22:18

ChapMic

Works like a charm ! Thanks ! – bilbaoWon May 03 '12 at 22:22
3

Just note that without an `ORDER BY` you're going to get an arbitrary row, and this can change from execution to execution... – Aaron Bertrand May 03 '12 at 22:50

score 1 · Answer 2 · answered Jul 25 '17 at 02:57

1

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).

answered Jul 25 '17 at 02:57

Why do you say that `Top(1)` might not work in a nested statement? I'm curious about this, would you mind elaborating? – AnOccasionalCashew Mar 03 '18 at 06:39
Not sure, but I see that it's being discussed here too https://stackoverflow.com/questions/29358682/sql-top-1-syntax-for-a-nested-query – Mar 03 '18 at 11:03

2 Answers2