How I can limit result in SQL Server?

Question

I have table containing data for users, I need to get only last 2 users after first user ex

id-username
1-john
2-fredrek
3-sara
4-sarah

I need to get fredrek, sara - how do I do this in SQL Server ??

I know to do that with MySQL I use LIMIT1,2 but with SQL Server I can't

same thing already discussed: http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver — Senthil, Apr 10 '11 at 09:34

score 2 · Answer 1 · answered Apr 10 '11 at 09:40

2

To avoid 2 x TOP you can use ROW_NUMBER (note: you can't use ROW_NUMBER output directly in the WHERE clause of a single statement )

;WITH cRN AS
(
  SELECT
     ROW_NUMBER() OVER (ORDER BY id) AS rn,
     username
  FROM
     mytable
)
SELECT username
FROM cRN
WHERE rn BETWEEN 2 AND 3

answered Apr 10 '11 at 09:40

gbn

422,506
82
585
676

2

This is the easy solution. There is no LIMIT feature in SQL Server (until next version). – gbn Apr 10 '11 at 10:13
and i can use this way with php mssql_query(); – Kareem Nour Emam Apr 10 '11 at 11:12

score 1 · Answer 2 · answered Apr 10 '11 at 12:51

1

You can use TOP, restricting in the WEHRE clause, not to take the first register

SELECT TOP 2 *
FROM MyTable
WHERE Id > 1

answered Apr 10 '11 at 12:51

pcofre

3,976
18
27

score 0 · Answer 3 · edited Apr 10 '11 at 09:56

0

You can use TOP in T-SQL:

SELECT TOP 2 FROM table_name

edited Apr 10 '11 at 09:56

gbn

422,506
82
585
676

answered Apr 10 '11 at 09:24

ChristiaanV

5,401
3
32
42

score 0 · Answer 4 · answered Apr 10 '11 at 09:26

0

With SQL Server, you can use the TOP command.

answered Apr 10 '11 at 09:26

NKCSS

2,716
1
22
38

score 0 · Answer 5 · answered Apr 10 '11 at 09:32

0

You have to make a dual query:

   select * from ( select top 10 * from (
    select top 20 * from table_name order by 1 asc 
    ) as tb_name order by 1 desc
    ) as tb_name_last 
    order by whathever;

answered Apr 10 '11 at 09:32

pcmind

990
10
12

How I can limit result in SQL Server?

5 Answers5