To read the article online, visit http://www.4GuysFromRolla.com/webtech/110498-1.shtml

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

  • Article Information
    Article Title: 4GuysFromRolla.com - SQL Script to get top n values from a table
    Article Author: Scott Mitchell
    Published Date: Wednesday, November 04, 1998
    Article URL: http://www.4GuysFromRolla.com/webtech/110498-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers