Q

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

How can I select the third, fourth and fifth rows, out of a 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 editor@searchDatabase.com 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.

This was first published in April 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close