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?
370
30
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:
Oracle, DB2 and MSSQL supports the standard windowing functions:
PostgreSQL supports windowing functions as defined by the SQL standard, but they're awkward, so most people use (the non-standard)
LIMIT
/OFFSET
:This example selects the 21st row.
OFFSET 20
is telling Postgres to skip the first 20 records. If you don't specify anORDER BY
clause, there's no guarantee which record you will get back, which is rarely useful.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:
(of course, you'd need to have an added DATETIME field, and set it to the date/time that entry was added...)
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:
I suspect this is wildly inefficient but is quite a simple approach, which worked on a small dataset that I tried it on.
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().
Verify it on SQL Server:
This will give you 10th ROW of emp table!
1 small change: n-1 instead of n.
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.
When we used to work in MSSQL 2000, we did what we called the "triple-flip":
EDITED
It wasn't elegant, and it wasn't fast, but it worked.
Select n' th record from top
select n' th record from bottom
Oracle:
In Oracle 12c, You may use
OFFSET..FETCH..ROWS
option withORDER BY
For example, to get the 3rd record from top:
Here is a fast solution of your confusion.
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.
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.
ADD:
That will limit the results to one result starting at result n.
For example, if you want to select every 10th row in MSSQL, you can use;
Just take the MOD and change number 10 here any number you want.
For SQL Server, a generic way to go by row number is as such:
For Example:
This will return the 20th row's information. Be sure to put in the rowcount 0 afterward.
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.
Here's a generic version of a sproc I recently wrote for Oracle that allows for dynamic paging/sorting - HTH
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.