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
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading