|
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.
|
