It's easy getting the oldest person from a database:
SELECT name FROM database WHERE DOB = ( SELECT MIN(DOB) FROM database )
But how do you get the 10th oldest or 16th oldest?
In Nth maximum record in Sybase (13 February 2002), the general solution is given. The general solution actually works in all databases, not just Sybase. However, it is perhaps not as efficient as proprietary solutions such as LIMIT for MySQL and TOP for Microsoft Access and SQL Server.
Modifying the general solution slightly, to return the earliest date instead of largest salary, we get:
select name , DOB from yourTable X where n-1 = ( select count(*) from yourTable where DOB < X.DOB) order by DOB asc
In the above query, n is 10 or 16 or whatever. Be aware that you may get more than one result in case of ties.
In Select 3rd, 4th, 5th of 100 rows (10 April 2002), some shortcuts are offered.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.