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.