Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

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:

• Returing the Top X Values
• Retrieving the First N Records from a SQL Query
• Returning Ranked Results with Microsoft SQL Server 2005

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

By Scott Mitchell

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article