CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlrank
user614978
user614978

Reputation: 317

How to rank the table columns based on duplicates?

I've a table like this And I want to rank it based on combination of cksid and guid.

cksid       guid
----------- -----------
1           301
1           301
1           301
2           303
2           303
3           303
4           303
4           303
4           303

Output should be like 

cksid       guid           rank
----------- ----------- ----------
1           301             1
1           301             2
1           301             3
2           303             1
2           303             2
3           303             1
4           303             1
4           303             2
4           303             3

Upvotes: 2

Views: 334

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

You just have to use the row_number function 

 select cksid ,guid ,
    row_number() over (partition by cksid ,guid order by (select 0)) as rank
    from <Table>

row_number() requires an order by clause. Here you dont have a third column to order by. So I am just putting (Select 0) which will order the output in any order, which doesn't matter to us. We could have given order by cksid ,guid also , but I think which is a little over head as we can get the same result by just giving (select 0)

Upvotes: 5

AnandPhadke
AnandPhadke

Reputation: 13496

create table test11(cksid int,guid int)
INSERT INTO test11
VALUES(1,301),
(1,301),
(1,301),
(2,303),
(2,303),
(3,303),
(4,303),
(4,303),
(4,303)

select *,ROW_NUMBER() over (partition by cksid,guid order by guid) as ranks from test11

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36611

SELECT cksid, guid,
       row_number() OVER (PARTITION BY cksid,guid ORDER BY cksid,guid)
FROM   youtable;

Upvotes: 1

Related Questions