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

Question

I'm interested in learning some (ideally) database agnostic ways of selecting the nth 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

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

Answer 1

ADD:

LIMIT n,1

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

Answer 2

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.

Answer 3

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);

Answer 4

I'm not sure about any of the rest, but I know SQLite and MySQL don't have any "default" row ordering. In those two dialects, at least, the following snippet grabs the 15th entry from the_table, sorting by the date/time it was added:

SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15

(of course, you'd need to have an added DATETIME field, and set it to the date/time that entry was added...)

Answer 5

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().

Answer 6

Sorry I've been using SQL Server at my job for about 4 months, guess I haven't used a LIMIT clause yet.

I'd be interested to know how most people handle something like pagination on SQL Server based websites (hint hint Jeff and company)

Answer 7

The syntax in PostgreSQL is:

SELECT * FROM mytable ORDER BY somefield LIMIT 1 OFFSET 20;

Apparently, the SQL standard is silent on the limit issue, which is why everyone implements it differently.

Edit: Just to clarify - the example above selects the 21st row. "OFFSET 20" is telling Postgres to skip the first 20 records.

Answer 8

When we used to work in MSSQL 2000, we did what we called the "triple-flip":

SELECT * FROM
(
    SELECT TOP (pagesize) FROM
    (
        SELECT TOP (n * pagesize) FROM X ORDER BY (column) ASC
    ) AS t1 ORDER BY (column) DESC
) AS t2 ORDER BY (column) ASC

It wasn't elegant, and it wasn't fast, but it worked.

Answer 9

@Bill MySQL in MyISAM will fill up deleted rows. So there is no actual ordering. Of course you could create on yourself by using an auto incrementing id or priority.

Answer 10

Oracle:

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

Answer 11

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.

Answer 12

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.

Answer 13

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
OFFSET x LIMIT y

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Answer 14

In MySQL:

select *
from thetable
limit n, 1

That is why I love MySQL.

Answer 15

Contrary to what some of the answers claim, the SQL standard is not silent regarding this subject. Since SQL:2003, you have been able to use "window functions" to skip rows and limit result sets. And in SQL:2008--which has recently been approved--a sligthly simpler approach had been added, using "... OFFSET skip ROWS FETCH FIRST n ROWS ONLY". Personally, I don't think that SQL:2008's addition was really needed, so if I were ISO, I would have kept it out of an already rather large standard.

Answer 16

I was using the posted technique, but I wanted a random nth row from the first 100 rows for test data purposes and used

WITH sentence AS
(SELECT 
    stuff,
    row = ROW_NUMBER() OVER (ORDER BY Id)
FROM 
    SentenceType
    )
SELECT
    sen.stuff
FROM sentence sen
WHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1

This gives some very weird results, sometimes no results, sometimes multiple rows. Anyone the faintest idea why?

Answer 17

unbelievable that you can find a SQL engine executing this one ...

WITH sentence AS (SELECT stuff, row = ROW_NUMBER() OVER (ORDER BY Id) FROM SentenceType ) SELECT sen.stuff FROM sentence sen WHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1

Answer 18

Pour quoi?

this

DECLARE @row INT
SET @row = (ABS(CHECKSUM(NEWID())) % 100) + 1 --rand 1-100 this aint cryptography

;WITH sentence AS
(SELECT 
    stuff,
    row = ROW_NUMBER() OVER (ORDER BY Id)
FROM 
    SentenceType
    )
SELECT
    sen.stuff
FROM sentence sen
WHERE sen.row = @row

Will give you the random @row Nth row every time, so what's so terrible about the former example? SQL2008 fyi, but no reason why 2005 shouldn't do that.

I'm just trying to work out what's so weird in the CTE underbelly that makes it behave like that.

Answer 19

OK, got it.

In my first post it's calculating (ABS(CHECKSUM(NEWID())) % 100) + 1 against each row in the CTE, thus you will get as many (or as few) returned rows as you get coincidental matches between the (ABS(CHECKSUM(NEWID())) % 100) + 1 and the ROW_NUMBER. Interesting I always thought the where clause was considered a static value applied to each row, not a row level value applied to each row. Hmm beware of any getdate type stuff on a long running query I reckon.

One for the memory banks.

Answer 20

and shut me up soon, but I've always used the checksum modulo to generate random numbers BECAUSE RAND() is essentially as static function that will return the same 'random' number across a set.

In this case that will work fine because that's exactly what I'm after.

Mind you I'm trying to use this inline against a larger set based request which means I may end up with the opposite rpoblem, too predictable over too unpredictable, perhaps I should just hardcode my guid Id :(

Answer 21

SELECT * FROM emp a WHERE n = (SELECT COUNT( _rowid) FROM emp b WHERE a. _rowid >= b. _rowid);

Answer 22

For SQL Server, a generic way to go by row number is as such: SET ROWCOUNT @row --@row = the row number you wish to work on.

For Example:

set rowcount 20 --sets row to 20th row

select meat, cheese from dbo.sandwich --select columns from table at 20th row

set rowcount 0 --sets rowcount back to all rows

This will return the 20th row's information. Be sure to put in the rowcount 0 afterward.

I know noobish, but I am a SQL noob and I have used it so what can I say?

Answer 23

SQL 2005 and above has this feature built-in. Use the ROW_NUMBER() function. It is excellent for web-pages with a << Prev and Next >> style browsing:

Syntax:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY MyColumnToOrderBy) AS RowNum, * FROM Table_1) sub
WHERE RowNum = 23

Answer 24

Hi Ben What is the "sub" you have used in your query? Is it key word?