INF: How to Dynamically Number Rows in a Select Statement

ID: Q186133

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

Row numbering or ranking is a typical, procedural issue. The solutions are generally based on loops and temporary tables and, therefore, SQL Server loops and cursors. This article describes how to dynamically rank rows when performing a SELECT statement. It shows a flexible method, which may be the only possible solution. It is faster than the procedural solution.

MORE INFORMATION

This technique is based on an AUTO JOIN. The chosen relationship is generally "is greater than." The goal is to count how many times each element of a given set of data fulfills the relationship "is greater than" when the set is compared to itself. The following examples are based on database pubs.

Example 1

Set 1 is authors.
Set 2 is authors.
The relationship is "last and first names are greater than."
We avoid the duplicate problem by comparing the first + last names to the other first + last names.
We count the number of times the relationship is fulfilled by count(*).

Query:


   select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by 1

Result:


   Rank        Au_Lname              Au_Fname
   ----        --------------        -----------
     1         Bennet                Abraham
     2         Blotchet-Halls        Reginald
     3         Carson                Cheryl
     4         DeFrance              Michel
     5         del Castillo          Innes
     6         Dull                  Ann
     7         Greene                Morningstar
     8         Green                 Marjorie
     9         Gringlesby            Burt
    10         Hunter                Sheryl
    11         Karsen                Livia
    12         Locksley              Charlene
    13         MacFeather            Stearns
    14         McBadden              Heather
    15         O'Leary               Michael
    16         Panteley              Sylvia
    17         Ringer                Albert
    18         Ringer                Anne
    19         Smith                 Meander
    20         Straight              Dean
    21         Stringer              Dirk
    22         White                 Johnson
    23         Yokomoto              Akiko

   (23 row(s) affected)

Example 2

We want to rank stores by the number of books they have sold.

Set 1 is the number of books sold by store: select stor_id, qty=sum(qty) from sales group by stor_id.
Set 2 is the number of books sold by store: select stor_id, qty=sum(qty) from sales group by stor_id.
The relationship is "the number of books is greater than".
To avoid duplicates we could compare price*qty instead of qty for instance.

Query:


   select rank=count(*), s1.stor_id, qty=sum(s1.qty)
   from (select stor_id, qty=sum(qty) from sales group by stor_id) s1,
        (select stor_id, qty=sum(qty) from sales group by stor_id) s2
   where s1.qty >= s2.qty
   group by s1.stor_id
   order by 1

Result:


   Rank     Stor_Id    Qty
   ----     -------    ---
   1         6380        8
   2         7896      120
   3         8042      240
   4         7067      360
   5         7066      625
   6         7131      780

   (6 row(s) affected)

This example and some that follow only work in Microsoft SQL Server version 6.5, because they use derived tables in the FROM clause.

Example 3

We want to rank the publishers by their earnings.

Set 1 is the total sales by publisher:


        select t.pub_id, sales=sum(s.qty*t.price)
        from sales s, titles t
        where s.title_id=t.title_id
          and t.price is not null
        group by t.pub_id

Set 2 is the total sales by publisher:


        select t.pub_id, sales=sum(s.qty*t.price)
        from sales s, titles t
        where s.title_id=t.title_id
          and t.price is not null
        group by t.pub_id

The relationship is "earns more money than."

Query:


   select rank=count(*), s1.pub_id, sales=sum(s1.sales)
   from    (select t.pub_id, sales=sum(s.qty*t.price)
           from sales s, titles t
           where s.title_id=t.title_id
             and t.price is not null
           group by t.pub_id) s1,
           (select t.pub_id, sales=sum(s.qty*t.price)
           from sales s, titles t
           where s.title_id=t.title_id
             and t.price is not null
           group by t.pub_id) s2
   where s1.sales>= s2.sales
   group by s1.pub_id
   order by 1

Result:


   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)

Drawbacks

Because of the CROSS JOIN, this is not designed for working with a large number of rows. It works well for tens or hundreds of rows. On large tables, be sure to use an index to avoid large scans.

This does not work well with duplicate values. In other words, when comparing duplicate values, discontinuous row numbering occurs. If this is not the desired behavior, you can avoid it by hiding the rank column when you insert the result in a spreadsheet; use the spreadsheet numbering instead.

Example:


   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id
   order by 1

Result:


   Rank    Title_Id    Qty
   ----    --------    ----
   1       MC2222        10
   4       BU1032        60
   4       BU7832        60
   4       PS3333        60
   7       PS1372       140
   7       TC4203       140
   7       TC7777       140
   10      BU1111       250
   10      PS2106       250
   10      PS7777       250
   11      PC1035       330
   12      BU2075       420
   14      MC3021       560
   14      TC3218       560
   15      PC8888       750
   16      PS2091      1728

   (16 row(s) affected)

Benefits

These queries can be used in views and result formatting. The lower-ranked data can be shifted farther to the right.

Example 1:


   CREATE VIEW v_pub_rank
   AS
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id

Query:


   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
           pub_id +
           replicate(' ', 15-power(2,rank))+': '),
           earnings=qty
   from v_pub_rank

Result:


   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85

Example 2:


   CREATE VIEW v_title_rank
   AS
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id

Query:


   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank

Result:


   Book                                          Qty
   -------------------------------------------   ----
     MC2222                                 :      10
           BU1032                           :      60
           BU7832                           :      60
           PS3333                           :      60
                 PS1372                     :     140
                 TC4203                     :     140
                 TC7777                     :     140
                       BU1111               :     250
                       PS2106               :     250
                       PS7777               :     250
                         PC1035             :     330
                           BU2075           :     420
                               MC3021       :     560
                               TC3218       :     560
                                 PC8888     :     750
                                   PS2091   :    1728

   (16 row(s) affected)

Additional query words: prodsql join rank formatting query


Keywords          : SSrvTran_SQL 
Version           : WinNT:4.2x,6.0,6.5
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: April 16, 1999