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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, November 04, 1998

SQL Script: Obtaining Ranked Values from a Table
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:

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
ItemNumberIDint
Costmoney

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] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article