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

Retrieving row numbers with SQL

A SearchOracle.com member asks, "How do you retrieve row numbers along with the data?"

How do you retrieve row numbers along with the data?

Some database management systems (DBMSs) have a built-in function for this, like Oracle's ROWNUM. Alternatively, the DBMS might support SQL analytic functions, of which ROW_NUMBER is one. Note, however, that ROW_NUMBER requires the use of an ORDER BY parameter in the OVER clause. For example:

select empname
     , row_number() 
          over( order by birthdate desc )
  from employees

Here the row number increases from the youngest employee to the oldest. Note that row number is not the same as rank, which takes ties into consideration. You could also use a subquery to determine rank:

select empname
     , ( select count(*) + 1
           from employees
          where salary > t.salary ) as rank
  from employees as t

Here the rank increases for each distinct salary. Caution: this type of query, with a theta join correlated subquery, can be rather inefficient.

The best advice for row numbers, however, is to compute them with a running counter in your application code layer, not in the SQL.

Dig Deeper on Oracle development languages

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.