Needed a SQL select query, with a row column which is incremented only when a row's id column is unique

Question

Given a table:

#  Id           Val
1  1111111111   Andre
2  1111111111   Bart
3  1111111111   Corry
4  2222222222   Donald
5  2222222222   Eric
6  3333333333   Fiona

I want to select the table with an indicator: to group rows which have the same id, namely get something like:

#  Id           Val
1  1111111111   Andre
1  1111111111   Bart
1  1111111111   Corry
2  2222222222   Donald
2  2222222222   Eric
3  3333333333   Fiona

In this I came up with the solution:

SELECT 
(
  row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY id ORDER BY id) + 1
) nGroupedToRow,
row_number() OVER (ORDER BY id) nRow,
row_number() OVER (PARTITION BY id ORDER BY id) nNumDupl,
   id, 
   val 
FROM table1  

which produces:

nGroupedToRow nRow  nNumDupl Id           Val
1             1     1        1111111111   Andre
1             2     2        1111111111   Bart
1             3     3        1111111111   Corry
4             4     1        2222222222   Donald
4             5     2        2222222222   Eric
6             6     1        3333333333   Fiona

Thought this may help someone... and maybe someone can improve on it... maybe a similar question has been asked but I couldn't find it though other solutions on stack-overflow lead me to this one...

http://sqlfiddle.com/#!18/b52aba/1

Answer 1

SELECT 
(
  row_number() OVER (ORDER BY id) - row_number() 
               OVER (PARTITION BY id ORDER BY id) + 1
) nGroupedToRow,
    row_number() OVER (ORDER BY id) nRow,
    row_number() OVER (PARTITION BY id ORDER BY id) nNumDupl,
    id, 
    val 
FROM table1  

http://sqlfiddle.com/#!18/b52aba/1