When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Wednesday, November 04, 1998

SQL Script: Obtaining Ranked Values from a Table
By Scott Mitchell

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:

However, if you are interested in being able to access the contents of a table in rank order, read on....

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

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

  • By Scott Mitchell

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article