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.
No Comments