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 nu
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
mber 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.
Oracle White Papers: Fusion Middleware