Q

Running number in query results

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

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close