The SQL Server equivalent for the MySQL Limit

Question

Possible Duplicate:
Equivalent of LIMIT and OFFSET for SQL Server?

this is a mysql query

SELECT email FROM emailTable LIMIT 9,20 WHERE id=3

how do you write it in SQL Server (2008)?

do I have to write

SELECT TOP 9,20 email FROM emailTable WHERE id=3

? Thank you for your help

http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — emboss, Feb 13 '12 at 13:20
Thank you but it was no use, since It doesnt answer my problem — Bellash, Feb 13 '12 at 13:29

score 6 · Accepted Answer · edited Jan 18 '21 at 12:30

6

Try this:

SELECT * 
FROM 
( 
      SELECT *, 
        ROW_NUMBER() OVER (ORDER BY email) as rowNum 
      FROM tableName
) sub 
WHERE rowNum > 9 
  AND rowNum <= 9 + 20 
  AND id = 3

DEMO

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 13 '12 at 13:21

Mahmoud Gamal

72,639
16
129
156

1

I fixed your query to match the OP's behaviour. In MySQL, the syntax is `LIMIT offset, rowcount`. That means you have to do `rownum > offset and rownum <= offset + rowcount` – Lukas Eder Feb 13 '12 at 13:27
@LukasEder, Thanks for editing. – Mahmoud Gamal Feb 13 '12 at 13:30
So, I can't see the email column – Bellash Feb 13 '12 at 13:34
@AdaBell'hMonTrésor, No you can select the columns you want in the select clause, see the demo page . – Mahmoud Gamal Feb 13 '12 at 13:49
Thanks for this - helped me too – Paolo Broccardo Oct 26 '12 at 19:19

AdaTheDev · Answer 2 · 2012-02-13T13:42:15.497

2

You can use ROW_NUMBER() to achieve the same outcome.

e.g. to get rows 1 to 10:

;WITH results AS
(    
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn ASC) AS RowNo,
        email
    FROM emailTable
)

SELECT * 
FROM results
WHERE RowNo BETWEEN 1 AND 10

Edit: condensed into 1 statement:

SELECT * 
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn ASC) AS RowNo,
        email
    FROM emailTable
) x
WHERE x.RowNo BETWEEN 1 AND 10

edited Feb 13 '12 at 13:42

answered Feb 13 '12 at 13:21

AdaTheDev

128,935
26
185
187

1

thank you AdaTheDev but can you do it basically? I mean just select email for 20 first users whose id=3, starting by the 9th... maybe you catch me well – Bellash Feb 13 '12 at 13:37
It isn't as straight forward as there isn't support for LIMIT. There are other variations of this, but none as as "basic" as LIMIT. e.g. the above could be condensed into 1 SELECT statement, as updated in my answer – AdaTheDev Feb 13 '12 at 13:41

Somnath Muluk · Answer 3 · 2012-02-13T14:15:48.837

1

Limit with offset in sql server:

SELECT email FROM emailTable WHERE id=3
OFFSET 8 ROWS
FETCH NEXT 12 ROWS ONLY;

//offset - no. of skipped rows

//next - required no. of next rows

Update: This is working but sql server 2012 onwards

edited Feb 13 '12 at 14:15

answered Feb 13 '12 at 13:41

Somnath Muluk

46,917
28
204
217

MS SQL Server 2012 (not yet released) – Marcus Adams Feb 13 '12 at 13:56
This is what I was looking for! Thanx a lot Adams BUT ... – Bellash Feb 13 '12 at 14:01
Offset is no. of rows want to skip. He want results from 9 – Somnath Muluk Feb 13 '12 at 14:06

The SQL Server equivalent for the MySQL Limit

3 Answers3

DEMO