Enumerate Rows in SQL

The following SQL is an example of how to enumerate (number) rows in SQL. This SQL will only work on Oracle databases.

select
    ROW_NUMBER () OVER (partition by ROLEUSER order by ROLENAME) as SEQNO,
    ROLEUSER,
    ROLENAME
from PSROLEUSER
order by ROLEUSER;

In this example, the roles for each user will be enumerated.

So for example, the results for the user PS would be something like this (roles 1, 2, 3 … n)

SEQNO ROLEUSER ROLENAME
1 PS ADHOCUSR
2 PS AM Administrator
3 PS AppServer Administrator
n PS Rolename

This SQL is very useful if you need to insert rows with a sequence number or effective sequence number into a table.

sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License