CODEWITHSUNDEEP
sqlsql-server-2012
RedRocket
RedRocket

Reputation: 1733

Use Dense_Rank to show row number in sql

I have a table showed below

table_1

  col_a        col_b
     a           ab
     a           ab
     a           ab  
     a           bc
     b           bc
     b           bc
     b           bc

I want to show the row number like this 

row_num  col_a  col_b
    1    a     ab
    1    a     ab
    1    a     ab
    2    a     bc
    3    b     bc
    3    b     bc
    3    b     bc

I want to structure my table looks like this. I used Dense_Rank(), by it is not working. Help will be appreciated

Here is my code.

DENSE_RANK() OVER (
    Partition BY col_a,col_b
            Order by 
            col_a
           ),

But I get this result instead.

row_num  col_a  col_b
    1    a     ab
    1    a     ab
    1    a     ab
    1    a     bc
    1    b     bc
    1    b     bc
    1    b     bc

Upvotes: 0

Views: 40

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Just order by col_a and col_b:

WITH table_1(col_a, col_b) AS(
    SELECT * FROM (VALUES
        ('a',   'ab'),
        ('a',   'ab'),
        ('a',   'ab'),
        ('a',   'bc'),
        ('b',   'bc'),
        ('b',   'bc'),
        ('b',   'bc')
    )t(a,b)
)
SELECT *,
    DENSE_RANK() OVER(ORDER BY col_a, col_b)
FROM table_1

Upvotes: 1

Related Questions