How to select nth row in SQL Server 2005 database query

Question

SQL Server 2005 query is as follows......

SELECT ClgId 
FROM IdMaker_DB  
WHERE Course = 'B-Tech' AND [Class] = 'Ist Year'
AND Branch = 'Computer Science and Engineering' 
ORDER BY ClgId

It works fine and selects all entries of Computer Science and Engineering Ist Year but is select only first row, how can I select nth row......

This may be a duplicated question: You can find an answer here: http://stackoverflow.com/questions/187998/row-offset-in-sql-server — chris, Feb 02 '12 at 20:43

Royi Namir · Accepted Answer · 2012-02-02T20:39:45.627

3

select * from (
 SELECT ClgId ,row_number() over (order by ClgId) as rn  FROM IdMaker_DB 
where Course ='B-Tech' and [Class]='Ist Year'

 and Branch='Computer Science and Engineering'   ) a where rn=n   --replace the n

edited Feb 02 '12 at 20:39

answered Feb 02 '12 at 20:34

Royi Namir

144,742
138
468
792

row_number() is the key, but you will want to put it before the FROM. – Malk Feb 02 '12 at 20:37
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'over'. – Feb 02 '12 at 20:38
@Dinesh Gehlot please try now. – Royi Namir Feb 02 '12 at 20:38
@adam thats what happens when you re in phone + you wife talking to your ear.....:) – Royi Namir Feb 02 '12 at 20:42

score 0 · Answer 2 · answered Feb 02 '12 at 20:36

SELECT top 1 ClgId 
FROM IdMaker_DB  
where Course ='B-Tech' 
and [Class]='Ist Year'
and Branch='Computer Science and Engineering' 
and clgid not in 
    (SELECT top N - 1 ClgId 
    FROM IdMaker_DB  
    where Course ='B-Tech' 
    and [Class]='Ist Year'
    and Branch='Computer Science and Engineering' 
    ORDER BY ClgId)
ORDER BY ClgId

score -3 · Answer 3 · edited Sep 09 '13 at 00:10

-3

SELECT TOP 1 * 
FROM   student 
WHERE  id IN 
       ( 
        SELECT TOP n id 
        FROM student 
       ) 
ORDER BY id DESC

edited Sep 09 '13 at 00:10

Leigh

28,765
10
55
103

answered Sep 08 '13 at 11:55

Deepak

1

1

This does not provide a correct answer to the question asked. Using `TOP` without an `ORDER BY` produces results of an [undefined order](http://technet.microsoft.com/en-us/library/ms189463.aspx). Not sure why this was posted, seeing as how two valid answers were already given ... over a year ago. – Leigh Sep 09 '13 at 00:24

How to select nth row in SQL Server 2005 database query

3 Answers3