Select 3rd, 4th, 5th of 100 rows

This is another variation of the "Top Ten" problem. As you may know, all sequencing in a relational database must be based on the value of some column -- there simply is no "third, fourth, or fifth record" unless you say based on which column, and in which (ascending or descending) order. So let's say we want to consider the highest values (descending order) of column1.

Also, you did not say which database system you are using. If you're using MySQL, you can get exactly the rows you want --

select column1, column2, column3
  from yourTable 
order by column1 descending limit 2,3

The LIMIT keyword has two parameters -- offset and number of rows to return. The first row has offset 0, so LIMIT 2,3 gives rows 3, 4, and 5.

If it's Microsoft SQL/Server or Access, use the TOP keyword to restrict the return to five rows --

select top 5 column1, column2, column3
  from yourTable 
order by column1 descending

... and then simply discard the first and second rows!

If it's some other database, consider just returning all rows, because 100 isn't really all that many -- again, simply discard the ones you don't need.

Otherwise, if you cannot discard rows, you can use the following SQL to get exactly the rows you want --

select column1, column2, column3
  from yourTable ZZ
 where ( select count(*) 
           from yourTable 
          where column1 > ZZ.column1 ) 
       between 2 and 4
order by column1 descending

For each row of the ZZ outer table, the subquery counts how many rows have a higher column1 value. Any row that has between 2 and 4 rows with a higher value, must be the 3rd, 4th, or 5th row.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.