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?

Piles

Yes. Here's a link to information about the ISO SQL standard: troels.arvin.dk/db/rdbms/links/#standards – Multiracial

Just to point out that by the definition of a relation, rows in a table do not have order, so the Nth row in a table can not be selected. What can be selected is Nth row in a row-set returned by (the rest of) a query, which is what your example and all other answers accomplish. To most this may just be semantics, but it points to the underlying problem of the question. If you do need to return OrderNo N , then introduce an OrderSequenceNo column in the table and generate it from an independent sequence generator upon creating a new order. – Granniah

The SQL standard defines the option offset x fetch first y rows only. Currently supported by (at least) Postgres, Oracle12, DB2. – Cordiecordier

Answer 1

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...
LIMIT y OFFSET x 

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.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Ciborium

MySQL uses the OFFSET and LIMIT syntax also. Firebird uses FIRST and SKIP keywords, but they are placed right after SELECT. – Chimene

Shouldn't that be WHERE rownumber = n to get the nth row only? – Sniff

MySQL supports window functions since version 8. MariaDB since version 10.2 – Boatsman

SQL Server has support for the ansi OFFSET/FETCH syntax now. – Pelecypod

Answer 2

PostgreSQL supports windowing functions as defined by the SQL standard, but they're awkward, so most people use (the non-standard) LIMIT / OFFSET:

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

This example selects the 21st row. OFFSET 20 is telling Postgres to skip the first 20 records. If you don't specify an ORDER BY clause, there's no guarantee which record you will get back, which is rarely useful.

Upandcoming

Answer 3

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

Kathrynkathryne

This looks like the best way to limit the query with inline offset value. But shouldn't we use 0,14 here? 1,15 will leave the first row. – Sastruga

What does the 15 mean though? I know the 1 says to get one record. The comma isn't used in the example I checked out 1keydata.com/sql/sql-limit.html – Liebermann

Actually, from here php.about.com/od/mysqlcommands/g/Limit_sql.htm, if you wanted to grab the 15th entry wouldn't you do LIMIT 14, 1 (0th is the first element, 1 of length – Liebermann

it sholud be SELECT * FROM the_table ORDER BY added DESC LIMIT 15,1 – Storehouse

Answer 4

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

Clifford

Answer 5

Verify it on SQL Server:

Select top 10 * From emp 
EXCEPT
Select top 9 * From emp

This will give you 10th ROW of emp table!

Bellybutton

You have already provided a answer to this question here Delete that answer which you think is not right.If you think both the answers are right then post the both the answers in one place – Bolero

Answer 6

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, a slightly 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.

Multiracial

Its nice that there is a standard though, makes people like myself's life easier, and so nice of microsoft to do stuff in a standard way :) – Pacha

Answer 7

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

Collude

I'm going to use this as ROW_NUMBER() does not work in SQL 2000 (yes, we have a client on SQL 2000 still) Specifically, I'm going to replace the '5' with a iterator variable of a loop and use that to copy and modify each row of a table in turn. Maybe someone will see this comment and find this useful – Frenzy

Answer 8

1 small change: n-1 instead of n.

select *
from thetable
limit n-1, 1

Cruet

which technology? – Pacha

Answer 9

SQL SERVER

---

Select n' th record from top

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

select n' th record from bottom

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

Garcon

Answer 10

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

EDITED

DECLARE @InnerPageSize int
DECLARE @OuterPageSize int
DECLARE @Count int

SELECT @Count = COUNT(<column>) FROM <TABLE>
SET @InnerPageSize = @PageNum * @PageSize
SET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)

IF (@OuterPageSize < 0)
    SET @OuterPageSize = 0
ELSE IF (@OuterPageSize > @PageSize)
    SET @OuterPageSize = @PageSize

DECLARE @sql NVARCHAR(8000)

SET @sql = 'SELECT * FROM
(
    SELECT TOP ' + CAST(@OuterPageSize AS nvarchar(5)) + ' * FROM
    (
        SELECT TOP ' + CAST(@InnerPageSize AS nvarchar(5)) + ' * FROM <TABLE> ORDER BY <column> ASC
    ) AS t1 ORDER BY <column> DESC
) AS t2 ORDER BY <column> ASC'

PRINT @sql
EXECUTE sp_executesql @sql

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

Possible

Say you have 25 rows and you want the third page of 10 row pagesize, i.e. rows 21-25. The innermost query gets the top 30 rows (rows 1-25). The middle query gets the last 10 rows (rows 25-16). The outer query reorders them and returns rows 16-25. This is clearly wrong if you wanted rows 21-25. – Deon

Now it doesn't work if we want a middle page. Say we have 25 rows and we want the second page, i.e. rows 11-20. The inner query gets the top 2*10 = 20 rows, or rows 1-20. The middle query gets the last 15 rows: 25-((2-1)*10) = 15, which yields rows 20-6. The last query reverses the order and returns rows 6-20. This technique does not work, unless the total number of rows is a multiple of your desired page size. – Deon

Perhaps the best conclusion is that we should upgrade any remaining MS SQL Server 2000 instances. :-) It's nearly 2012, and this problem has been solved in better ways for many years! – Deon

@Bill Karwin: Note the IF / ELSE IF blocks below the OuterPageSize calculation - on pages 1 and 2, they will drop the OuterPageSize value back to 10. On page 3 (rows 21-25) the calculation will correctly return 5, and on all pages 4 and greater, the negative result from the calculation will be replaced by 0 (though it would probably just be quicker to return an empty data row immediately at that point). – Possible

Oh I see now. Well, I stand by my opinion that using MS SQL Server 2000 today is not worth the trouble. – Deon

Answer 11

Here is a fast solution of your confusion.

SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1

Here You may get Last row by Filling N=0, Second last by N=1, Fourth Last By Filling N=3 and so on.

This is very common question over the interview and this is Very simple ans of it.

Further If you want Amount, ID or some Numeric Sorting Order than u may go for CAST function in MySQL.

SELECT DISTINCT (`amount`) 
FROM cart 
ORDER BY CAST( `amount` AS SIGNED ) DESC 
LIMIT 4 , 1

Here By filling N = 4 You will be able to get Fifth Last Record of Highest Amount from CART table. You can fit your field and table name and come up with solution.

Cacka

Answer 12

In Oracle 12c, You may use OFFSET..FETCH..ROWS option with ORDER BY

For example, to get the 3rd record from top:

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

Quadrennium

Answer 13

Oracle:

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

Batton

where ROWNUM = x will only work for x = 1 in Oracle DB. i.e. where ROWNUM = 2 will not return any rows. – Punishment

Answer 14

ADD:

LIMIT n,1

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

Choli

Answer 15

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.

Trifid

Answer 16

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.

Diabolize

Answer 17

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

Bowlin

Answer 18

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.

Extol

Answer 19

Nothing fancy, no special functions, in case you use Caché like I do...

SELECT TOP 1 * FROM (
  SELECT TOP n * FROM <table>
  ORDER BY ID Desc
)
ORDER BY ID ASC

Given that you have an ID column or a datestamp column you can trust.

Charades

Answer 20

For SQL server, the following will return the first row from giving table.

declare @rowNumber int = 1;
    select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
    select TOP(@rowNumber - 1) * from [dbo].[someTable];

You can loop through the values with something like this:

WHILE @constVar > 0
BEGIN
    declare @rowNumber int = @consVar;
       select TOP(@rowNumber) * from [dbo].[someTable];
    EXCEPT
       select TOP(@rowNumber - 1) * from [dbo].[someTable];  

       SET @constVar = @constVar - 1;    
END;

Egotism

Answer 21

To select the nth row up to the mth row within a specific range from a table in SQL, you can use the LIMIT clause along with the OFFSET clause. The OFFSET clause specifies the number of rows to skip before starting to return the result set, and the LIMIT clause specifies the maximum number of rows to be returned. Here's an example of an SQL query to select the rows within a specific range:

SELECT *
FROM your_table_name
LIMIT (m - n + 1) OFFSET (n - 1);

Replace your_table_name with the actual name of your table, n with the starting row number, and m with the ending row number within the desired range. Note that the offset value is (n - 1) because the rows are zero-indexed, and the limit value is (m - n + 1) to retrieve the desired number of rows within the range.

For example, if you want to select rows 3 to 7 from a table, you would replace (n - 1) with (3 - 1) and (m - n + 1) with (7 - 3 + 1). This would result in OFFSET 2 LIMIT 5. This query will retrieve the rows within the specified range from the table.

Make sure to execute this SQL query in a database management system or tool that supports SQL.

Sedulity

Answer 22

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

Rosenzweig

Answer 23

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.

Kendre

Except for Oracle, DB2, well just about every enterprise level database in the entire world. PostgreSQL is about the only enterprise capable database that supports the LIMIT keyword, and that's mostly because being open source it needs to be approachable by the ACID ignorent MySQL crowd. – Oryx

@AlexD This "answer" was posted back in the old days of Stackoverflow before comments were implemented. I would have posted this as a comment to another answer, but at time time, comments didn't exist. – Kendre

Answer 24

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.

Troublemaker

Answer 25

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

Scirrhus

Answer 26

SELECT
    top 1 *
FROM
    table_name
WHERE
    column_name IN (
        SELECT
            top N column_name
        FROM
            TABLE
        ORDER BY
            column_name
    )
ORDER BY
    column_name DESC

I've written this query for finding Nth row. Example with this query would be

SELECT
    top 1 *
FROM
    Employee
WHERE
    emp_id IN (
        SELECT
            top 7 emp_id
        FROM
            Employee
        ORDER BY
            emp_id
    )
ORDER BY
    emp_id DESC

Sampling

Answer 27

I'm a bit late to the party here but I have done this without the need for windowing or using

WHERE x IN (...)

SELECT TOP 1
--select the value needed from t1
[col2]
FROM
(
   SELECT TOP 2 --the Nth row, alter this to taste
   UE2.[col1],
   UE2.[col2],
   UE2.[date],
   UE2.[time],
   UE2.[UID]
   FROM
   [table1] AS UE2
   WHERE
   UE2.[col1] = ID --this is a subquery 
   AND
   UE2.[col2] IS NOT NULL
   ORDER BY
   UE2.[date] DESC, UE2.[time] DESC --sorting by date and time newest first
) AS t1
ORDER BY t1.[date] ASC, t1.[time] ASC --this reverses the order of the sort in t1

It seems to work fairly fast although to be fair I only have around 500 rows of data

This works in MSSQL

Caterer

Answer 28

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

Lorislorita

Answer 29

This is how I'd do it within DB2 SQL, I believe the RRN (relative record number) is stored within the table by the O/S;

SELECT * FROM (                        
               SELECT RRN(FOO) AS RRN, FOO.*
               FROM FOO                         
               ORDER BY RRN(FOO)) BAR             
WHERE BAR.RRN = recordnumber

Sawtelle

Answer 30

select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

First select top 100 rows by ordering in ascending and then select last row by ordering in descending and limit to 1. However this is a very expensive statement as it access the data twice.

Lois

Answer 31

It seems to me that, to be efficient, you need to 1) generate a random number between 0 and one less than the number of database records, and 2) be able to select the row at that position. Unfortunately, different databases have different random number generators and different ways to select a row at a position in a result set - usually you specify how many rows to skip and how many rows you want, but it's done differently for different databases. Here is something that works for me in SQLite:

select * 
from Table 
limit abs(random()) % (select count(*) from Words), 1;

It does depend on being able to use a subquery in the limit clause (which in SQLite is LIMIT <recs to skip>,<recs to take>) Selecting the number of records in a table should be particularly efficient, being part of the database's meta data, but that depends on the database's implementation. Also, I don't know if the query will actually build the result set before retrieving the Nth record, but I would hope that it doesn't need to. Note that I'm not specifying an "order by" clause. It might be better to "order by" something like the primary key, which will have an index - getting the Nth record from an index might be faster if the database can't get the Nth record from the database itself without building the result set.

Whyte

Answer 32

Most suitable answer I have seen on this article for sql server

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3

Hjerpe

Answer 33

If you want to look at native functionalities: MySQL, PostgreSQL, SQLite, and Oracle (basically SQL Server doesn't seem to have this function) you could ACTUALLY use the NTH_VALUE window function. Oracle Source: Oracle Functions: NTH_VALUE

I've actually experimented with this in our Oracle DB to do some comparing of the first row (after ordering) to the second row (again, after ordering). The code would look similar to this (in case you don't want to go to the link):

SELECT DISTINCT dept_id
     , NTH_VALUE(salary,2) OVER (PARTITION BY dept_id ORDER BY salary DESC
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
        AS "SECOND HIGHEST"
     , NTH_VALUE(salary,3) OVER (PARTITION BY dept_id ORDER BY salary DESC
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS "THIRD HIGHEST"
  FROM employees
 WHERE dept_id in (10,20)
 ORDER 
    BY dept_id;

I've found it quite interesting and I wish they'd let me use it.

Rhone

Answer 34

WITH r AS (
  SELECT TOP 1000 * FROM emp
)
SELECT * FROM r
EXCEPT
SELECT TOP 999 FROM r

This will give the 1000th row in SQL Server.

Shears