DustinZhang DustinZhang - 1 year ago 55
SQL Question

How to get an index of different category returned by "order by" sql in oracle?

We can easily get a sql result as following:

SQL>select Name, Value from table order by Name;

Name Value
A 1
A 2
B 1
C 5
C 6
C 7

However, is there a way to link the name to a number so that an index of different names can be formed? Suppose we don't know how many different names are in the table and don't know what they are.

Name Value idx
A 1 0
A 2 0
B 1 1
C 5 2
C 6 2
C 7 2

Answer Source

This can easily be done using a window function:

select Name, 
       dense_rank() over (order by name) - 1 as idx
from table 
order by Name;