How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the *n*th row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:

- SQL Server
- MySQL
- PostgreSQL
- SQLite
- Oracle

I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

Credit for the above SQL: [Firoz Ansari's Weblog][1]

**Update:** See [Troels Arvin's answer][2] regarding the SQL standard. *Troels, have you got any links we can cite?*


[1]:

[To see links please register here]

[2]:

[To see links please register here]

ADD:

LIMIT n,1

That will limit the results to one result starting at result n.

LIMIT n,1 doesn't work in MS SQL Server. I think it's just about the only major database that doesn't support that syntax. To be fair, it isn't part of the SQL standard, although it is so widely supported that it should be. In everything except SQL server LIMIT works great. For SQL server, I haven't been able to find an elegant solution.

Here's a generic version of a sproc I recently wrote for Oracle that allows for dynamic paging/sorting - HTH

-- p_LowerBound = first row # in the returned set; if second page of 10 rows,
-- this would be 11 (-1 for unbounded/not set)
-- p_UpperBound = last row # in the returned set; if second page of 10 rows,
-- this would be 20 (-1 for unbounded/not set)

OPEN o_Cursor FOR
SELECT * FROM (
SELECT
Column1,
Column2
rownum AS rn
FROM
(
SELECT
tbl.Column1,
tbl.column2
FROM MyTable tbl
WHERE
tbl.Column1 = p_PKParam OR
tbl.Column1 = -1
ORDER BY
DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 1, Column1, 'X'),'X'),
DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 1, Column1, 'X'),'X') DESC,
DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate),
DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate) DESC
))
WHERE
(rn >= p_lowerBound OR p_lowerBound = -1) AND
(rn <= p_upperBound OR p_upperBound = -1);

I suspect this is wildly inefficient but is quite a simple approach, which worked on a small dataset that I tried it on.

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

This would get the 5th item, change the second top number to get a different nth item

SQL server only (I think) but should work on older versions that do not support ROW_NUMBER().

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x

But really, isn't all this really just parlor tricks for good database design in the first place? The few times I needed functionality like this it was for a simple one off query to make a quick report. For any real work, using tricks like these is inviting trouble. If selecting a particular row is needed then just have a column with a sequential value and be done with it.

In Sybase SQL Anywhere:

SELECT TOP 1 START AT n * from table ORDER BY whatever

Don't forget the ORDER BY or it's meaningless.

T-SQL - Selecting N'th RecordNumber from a Table


select * from
(select row_number() over (order by Rand() desc) as Rno,* from TableName) T where T.Rno = RecordNumber

Where RecordNumber --> Record Number to Select
TableName --> To be Replaced with your Table Name

For e.g. to select 5 th record from a table Employee, your query should be

select * from
(select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5

For example, if you want to select every 10th row in MSSQL, you can use;

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0

Just take the MOD and change number 10 here any number you want.