Q
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.

This was last published in January 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close