Using Row_Number to deal with non unique data

sql, sql-server-2008, sql-server-2012, t-sql

Solution

declare @t table(a int, b int, c int, d int)
insert @t values(1,1,1,1),(1,1,1,1),(1,1,1,2),
      (1,1,1,2),(1,1,1,3),(1,1,1,3),(1,1,1,4)    
select dense_rank() over(order by a,b,c,d) r, a,b,c,d from @t

Result:

r   a   b   c   d
1   1   1   1   1
1   1   1   1   1
2   1   1   1   2
2   1   1   1   2
3   1   1   1   3
3   1   1   1   3
4   1   1   1   4

Problem

I have 4 columns a,b,c,d. Some of my rows have the same values for all columns, is there any option to use row_number to insert same row number for those rows and continue counting if at least one of the values is different from values in the previous row Example: ``` a b c d 1 1 1 1 1 1 1 1 1 1 1 2 1 1 1 2 1 1 1 3 1 1 1 3 1 1 2 4 ``` I need it to look like: r=row_number ``` r a b c d 1 1 1 1 1 1 1 1 1 1 2 1 1 1 2 2 1 1 1 2 3 1 1 1 3 3 1 1 1 3 4 1 1 2 4 ``` P.S. How to write here something like a table?

Original source