Select nth row from a table in Sql server

How to select nth row from a table in sql server?

Say I have a table Items with primary key ItemId, I want to select the nth row something like, where row_number = 3.

But there is no function row_rumber, so how to get the nth row?

  • sql
  • sql server
3
 
Asked: 17 Jun 2013
Reputation: 59
Kaila Horne
4 Answers

If you are using sql server 2005 or latter then

WITH Temp AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY ItemId ASC) AS RowNo
      , ItemId 
    FROM Items 
)
SELECT Items.* 
FROM Items 
  INNER JOIN Temp ON Temp.ItemId = Items.ItemId 

WHERE RowNo = 3

I use 3 to select the row number 3, you can use any number

WHERE RowNo = n
6
 
Answered: 17 Jun 2013
Reputation: 830
Dev D
SELECT * FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY ItemId ASC) AS RowNumber,
      *
      FROM Items
 ) AS foo
WHERE RowNumber = N
4
 
Answered: 17 Jun 2013
Reputation: 1,643
Alicia Gonzalez

All the above answer will give the correct result from Dev, Alicia and Rabecca.

  1. If you will see closely the answer from Alicia, first it selects entire record and then only one record so if you will have millions of records then think the performance, so not a good answer,

  2. On the other hand Rebecca answer: will work well in your N is less but will decrease performance as you will increase the value of N because it first select N number of records and then one

  3. Finally Dev's Answer: is the best one, because it selects only the primary key column and adding a row number to every records, and It never selects entire columns so will searching will be fast of primary key and finally selecting one record on the basis of row number

So using a common table expression is a good choice

3
 
Answered: 20 Jun 2013
Reputation: 248
Barry John

First select N rows by using top (N) and top one by using order by desc

select Top 1 * From 
(
  Select Top (N) * from Items
  Order By ItemId  ASC
) AS foo
Order By ItemId  desc
2
 
Answered: 17 Jun 2013
Reputation: 79
Rebecca Lynch
Login to post your answer