How do I get the nth row in a SQL Server table?

Question

how would you get the 5 value from a row of data.

Answer 1

You have many choices, based on this link. All these techniques apply to SQL 2000. The one I've used before is option #1, where you take a 5-element subset ordered one way and then pick the "TOP" row out of the subset ordered the other way. Works great!

SELECT TOP 1 FName 
FROM 
( 
    SELECT TOP 5 FName 
    FROM Names 
    ORDER BY FName 
) sub 
ORDER BY FName DESC 

In SQL 2005 and up it's easier -- you have row ordering functions such as ROW_NUMBER() which will do what you need directly.

Answer 2

SQL Server 2005 and newer:

with Records AS(select row_number() over(order by datecreated) as 'row', * 
                from Table)
select * from records
where row=5

You can change the order by to determine how you sort the data to get the fifth row.

Tested on my local install: Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )

Answer 3

As of 2005, the ROW_NUMBER() function is available.

SELECT * FROM
(
   SELECT r.*, ROW_NUMBER() OVER (ORDER BY SomeField ASC) AS RowNum
) sorty
WHERE RowNum = 5

Answer 4

A BIT more/different than you asked for, but here is some stuff: Say you have a need to find the nth row of one table, on a specific datetime sequence and insert a value from it into another table if its unique identifier is not already in the other table. This gets the value from that first tables nth row. myuniquecol is an identifer/col that identifies the group of rows that all have the same value in the first table which have unique mydatetime values

( SELECT TOP 1 mycol FROM
    ( SELECT TOP 5 * FROM  mytable
        WHERE mytable.myuniquecol NOT IN (select myuniquecol from myothertable)
        AND mytable.myuniquecol = myuniquecol
        ORDER BY myuniquecol asc, mydatetime desc
    ) AS me
  ORDER BY myuniquecol desc, mydatetime asc
) AS mycolnew

This is useful to find the nth row in one table, and insert it in as the nth col in another table.

table1: myuniquecol, mycol, mydatetime

table2: myuniquecol, mycol1, mycol2, mycol3...

should work in any SQL :)

NOTE: mytable has a column called mycol

EDIT: took out some stuff because of SQL SERVER limitation on TOP

Answer 5

---

You can make use of Common Table Expression (CTE) and Window Function, ROW_NUMBER() (compatability from SQL 2005) to solver your problem.

Let us assume that the table name is tableOne and it has three columns (col1, col2, col3)

To make the definition complete, you need to specify the order with respect to which you want the rows numbered. The following scripts should help you get the nth row of the table.

---

--table definition
create table tableOne
(
    col1 varchar(10)
    ,col2 varchar(10)
    ,col3 varchar(10)
)
go
-- sample data creation
insert into tableOne VALUES ('c11','c12','c13')
insert into tableOne VALUES ('c21','c22','c23')
insert into tableOne VALUES ('c31','c32','c33')
insert into tableOne VALUES ('c41','c42','c43')
insert into tableOne VALUES ('c51','c52','c53')
insert into tableOne VALUES ('c61','c62','c63')
go
-- obtaining nth row using CTE and Window Function
WITH NthRowCTE AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY col1) AS RNum
        , *
    FROM tableOne
)
SELECT * FROM NthRowCTE WHERE RNum = 5
GO

---

WITH CTE and Window Function, you have the complete flexibility to output data the way you want.

Answer 6

If you are using SQL Serever 2005 or above you can use CTE functionality.

Otherwise you can use this simple query SELECT TOP 1 EmpID FROM
(
SELECT TOP 5 EmpID FROM EmpMaster ORDER BY Salary
) T ORDER BY Emp DESC

Visti my Blog: http://expertdevelopersblog.blogspot.com/