Search code examples
sqloracle-databaserow-number

Autonumber rows in select SQL based on column changes

I use a select all statement to retrieve all values from table A. Table A sample is the following: 

+---+----+---+
| a | 23 | X | 
+---+----+---+
| a | 23 | Y |
+---+----+---+
| a | 24 | X |
+---+----+---+
| a | 24 | Y |
+---+----+---+
| b | 24 | X |
+---+----+---+
| b | 24 | Y |
+---+----+---+
| b | 25 | X |
+---+----+---+
| b | 25 | Y |
+---+----+---+
| b | 25 | Z | 
+---+----+---+

For purposes in later stadium of this query, I would like to have a record number for each unique combination of column 1 and 2. For example: 

+---+----+---+---+
| a | 23 | X | 1 |
+---+----+---+---+
| a | 23 | Y | 2 |
+---+----+---+---+
| a | 24 | X | 1 |
+---+----+---+---+
| a | 24 | Y | 2 |
+---+----+---+---+
| b | 24 | X | 1 |
+---+----+---+---+
| b | 24 | Y | 2 |
+---+----+---+---+
| b | 25 | X | 1 |
+---+----+---+---+
| b | 25 | Y | 2 |
+---+----+---+---+
| b | 25 | Z | 3 |
+---+----+---+---+

Is this possible to do with SQL and how?

Solution

  • The description of your problem would use dense_rank():

    select t.*, dense_rank() over (order by col1, col2)
from t;

    Your sample data suggests dense_rank() with partitition by:

    select t.*,
       dense_rank() over (partition by col1, col2 order by col3) as seqnum
from t;