Q

The row with the Nth value in a column

It's easy getting the oldest person from a database. But how do you get the 10th oldest or 16th oldest?

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.

This was last published in November 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close