Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.