To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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.
|
