I am thinking about learning some (ideally) database agnostic methods for choosing the nth row from the database table. It might be also interesting to determine how this is often accomplished while using native functionality from the following databases:

  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle

I'm presently doing something similar to the next in SQL Server 2005, but I'd want to consider seeing other peoples more agnostic approaches:

WITH Purchased AS (

Choose ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate

FROM Orders)

Choose *

FROM Purchased

WHERE RowNumber = 1000000

Credit for that above SQL: Firoz Ansari's Website

Update: See Troels Arvin's answer concerning the SQL standard. Troels, do you have any links we are able to cite?

You will find methods for carrying this out in optional areas of the conventional, but many of databases support their very own method of doing the work.

An excellent site that discusses this along with other things is http://troels.arvin.dk/db/rdbms/#choose-limit.

Essentially, PostgreSQL and MySQL props up non-standard:

Choose...

LIMIT y OFFSET x

Oracle, DB2 and MSSQL props up standard windowing functions:

Choose * FROM (

  Choose

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,

    posts

  FROM tablename

) AS foo

WHERE rownumber <= n

(that we just replicated in the site linked above since i have never use individuals DBs)

Update: By PostgreSQL 8.4 the conventional windowing functions are supported, so expect the 2nd example to get results for PostgreSQL too.

The syntax in PostgreSQL is:

Choose * FROM mytable ORDER BY somefield LIMIT 1 OFFSET 20

Apparently, the SQL standard is quiet around the limit problem, and that's why everybody implements it in a different way.

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

I am unsure about the relaxation, however i know SQLite and MySQL haven't any "default" row ordering. In individuals two dialects, a minimum of, the next snippet grabs the 15th entry in the_table, sorting through the date/time that it was added:

Choose * In thedesk ORDER BY added DESC LIMIT 1,15

(obviously, you'd must have an additional DATETIME area, and hang it towards the date/time that entry was added...)

I suspect this really is extremely inefficient but is very an easy approach, which done a little dataset which i attempted it on.

choose top 1 area

from table

where area in (choose 5 best area from table order by area asc)

order by area desc

This could obtain the fifth item, alter the second top number to obtain a different nth item

SQL server only (I believe) but should focus on older versions that don't support ROW_NUMBER().

Whenever we accustomed to operate in MSSQL 2000, we did what we should known as the "triple-switch":

Choose * FROM

(

    Choose TOP (pagesize) FROM

    (

        Choose TOP (n * pagesize) FROM X ORDER BY (column) ASC

    ) AS t1 ORDER BY (column) DESC

) AS t2 ORDER BY (column) ASC

It had not been elegant, also it wasn't fast, however it labored.