By Scott Mitchell
Introduction
If you are interested in accessing the top N values from a table, there are two SQL Server keywords that you should
look into: TOP
and ROWCOUNT
. These are discussed in other articles here on 4Guys:
- Returing the Top X Values
- Retrieving the First N Records from a SQL Query
- Returning Ranked Results with Microsoft SQL Server 2005
Getting the Results of a Table with Rank
Here is a SQL Script to get the ranked values from a table. This SQL Script
uses a derived table in the FROM clause. (See Derived Tables in SQL for more
information on what derived tables are and how to use them...)
Let's say that we have a table which is defined as follows:
ItemCost Table | |
ItemNumberID | int |
Cost | money |
Here is the script which will return all of the rows in ranked order:
SELECT rank, ItemNumberID, Cost FROM (SELECT T1.ItemNumberID, T1.Cost, (SELECT COUNT(DISTINCT T2.Cost) FROM ItemCost T2 WHERE T1.Cost <= T2.Cost) AS rank FROM ItemCost T1) AS X ORDER BY rank
To get the top N rows, all you would need to do is put:
WHERE rank < N
right before your ORDER BY rank
.
The SQL query above is especially useful if you are interested in a particular subset of the ranked records. For example, you
can grab the second ranked item using WHERE rank = 2
. Or you could grab the 10th through 20th results using
WHERE rank BETWEEN 10 AND 20
.
Happy Programming!!