How can I get the n-th row in the Query results?

Question

How can I get the n-th row of a TSQL query results?

Let's say, I want to get the 2nd row of this SELECT:

SELECT * FROM table
ORDER BY 2 ASC

Answer 1

What version of SQL Server are you targeting? If 2005 or greater, you can use ROW_NUMBER to generate a row number and select using that number. http://msdn.microsoft.com/en-us/library/ms186734.aspx

WITH orderedtable AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY <your order here>) AS 'RowNumber'
    FROM table
) 
SELECT * 
FROM orderedtable 
WHERE RowNumber = 2;

Answer 2

You can use a trick combining TOP with ORDER BY ASC/DESC to achieve an effect similar to MySQL's LIMIT:

SELECT TOP 2 * INTO #temptable FROM table
ORDER BY 2 ASC

SELECT TOP 1 * FROM #temptable
ORDER BY 2 DESC

or without temptable, but nested statements:

SELECT TOP 1 * FROM
(
    SELECT TOP 2 * FROM table
    ORDER BY 2 ASC
) sub
ORDER BY 2 DESC

The first time you select all rows up to the one you want to actually have, and in the second query you select only the first of the remaining when ordering them reversely, which is exactly the one you want.

Source: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5

Answer 3

One way;

;with T(rownumber, col1, colN) as (
    select 
         row_number() over (order by ACOLUMN) as rownumber,
         col1,
         colN
    from 
         atable
)
select * from T where rownumber = 2