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

 

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?

    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.

    The LIMIT / OFFSET syntax in PostgreSQL is: 

    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.

    Apparently the SQL standard is silent on the limit issue outside of crazy windowing functions, which is why everyone implements it differently.

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

    链接地址: http://www.djcxy.com/p/16886.html

    上一篇: SQL只选择列上具有最大值的行

    下一篇: 如何选择SQL数据库表中的第n行？