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