11

How do I convert a MySQL query with LIMIT to a SQL Server query?

SELECT * 
FROM tableEating 
WHERE person = '$identity' 
LIMIT 1;
Mateen Ulhaq
  • 24,552
  • 19
  • 101
  • 135
bilbaoWon
  • 113
  • 1
  • 4

3 Answers3

13

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.

Mateen Ulhaq
  • 24,552
  • 19
  • 101
  • 135
ChapMic
  • 26,954
  • 1
  • 21
  • 20
  • 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
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).

  • 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
0

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 ...
James Risner
  • 5,451
  • 11
  • 25
  • 47
Dieter R
  • 1
  • 1