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

User · Answer

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

User · Answer

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

User · Answer

Nothing fancy  no special functions  in case you use Cach   like I do     SELECT TOP 1   FROM     SELECT TOP n   FROM  lt table gt    ORDER BY ID Desc   ORDER BY ID ASC   Given that you have an ID column or a datestamp column you can trust

User · Answer

1 small change  n-1 instead of n   select   from thetable limit n-1  1

User · Answer

SQL 2005 and above has this feature built-in  Use the ROW NUMBER   function  It is excellent for web-pages with a  lt  lt  Prev and Next    style browsing   Syntax   SELECT       FROM               SELECT             ROW NUMBER    OVER  ORDER BY MyColumnToOrderBy  AS RowNum                        FROM             Table 1       sub WHERE     RowNum   23

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt column gt   FROM  lt TABLE gt  SET  InnerPageSize    PageNum    PageSize SET  OuterPageSize    Count -    PageNum - 1     PageSize   IF   OuterPageSize  lt  0      SET  OuterPageSize   0 ELSE IF   OuterPageSize  gt   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  lt TABLE gt  ORDER BY  lt column gt  ASC       AS t1 ORDER BY  lt column gt  DESC   AS t2 ORDER BY  lt column gt  ASC   PRINT  sql EXECUTE sp executesql  sql   It wasn t elegant  and it wasn t fast  but it worked

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   p lowerBound OR p lowerBound   -1  AND      rn  lt   p upperBound OR p upperBound   -1

User · Answer

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  lt   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

User · Answer

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

User · Answer

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  lt recs to skip gt   lt recs to take gt   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

User · Answer

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

User · Answer

ADD   LIMIT n 1   That will limit the results to one result starting at result n

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Oracle   select   from  select foo from bar order by foo  where ROWNUM   x

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 -- gt  Record Number to Select        TableName -- gt  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

User · Answer

SELECT   FROM emp a WHERE  n    SELECT COUNT   rowid                FROM emp b              WHERE a   rowid  gt   b   rowid

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to postgresql