CODEWITHSUNDEEP
sqloracle-databaseselect
Tim Meyer
Tim Meyer

Reputation: 12600

How do I select sequential numbers for non-unique values?

I've got a column with non-unique values like so:

ID      COL_A
0       A
1       B
2       B
3       C
4       D
5       D
6       D
7       E

I would like to select an offset in addition to those two columns which produces the following output:

ID      COL_A     OFFSET
0       A         0
1       B         0
2       B         1
3       C         0
4       D         0
5       D         1
6       D         2
7       E         0

The offset should be applied so that the value with the lower primary key receives the lower offset.

I could probably come up with a PL/SQL approach to get this, but is this possible in pure SQL?

Upvotes: 1

Views: 53

Answers (2)

Mureinik
Mureinik

Reputation: 312146

The row_number() window function is just what the doctor prescribed:

SELECT id, col_a, ROW_NUMBER() OVER (PARTITION BY col_a ORDER BY id) - 1 AS offset
FROM   mytable

Upvotes: 3

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

USE ROW_NUMBER

SELECT COL1,COL2,ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY Col1) Seq
FROM TableNAme

Upvotes: 1

Related Questions