Ask the Expert

Running number in query results

How can I put a running number in a View based on a Special Field: *special_field*,*name*,*surname*?

    Requires Free Membership to View

    Login

If I understand the question correctly, you want a "running number" that basically numbers the rows in a result set, presumably sorted.

So your results might be -- 

Special                  Running 
 Field   Name  Surname   Number 
  AIF    John  Doe         1 
  BWH    Bill  Schmoe      2 
  CKR    Mary  Snow        3 
  KRU    Fred  Doh         4

This "running number" is also called a rank, and it is not easy to produce with SQL.

select Special_Field, Name, Surname 
     , (select count(*) from yourTable 
         where Special_Field <= OuterTable.Special_Field) as Running_Number 
  from yourTable as OuterTable

For each row of the table in the outer query, the inner query counts the number of rows which have a special field that is less than or equal to the special field of the row of the outer query under consideration. So if the outer query is looking at the lowest row, AIF, then there will be only one row with a special field equal to or less than AIF -- namely, the same row. Hence you need "equal to or less than" and not just "less than."

Caution: this query may not work as expected if there are "ties" in the rankings; I'm sorry, I haven't tested it recently, and I cannot say with certainty that ties are handled properly. Also, not all database systems support the embedded subquery in the SELECT list.


This was first published in July 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top