0

I need to get the unique combinations of 2 fields, with a RankID for each unique combination

In this example I need to create new IDs for each Customer/Value combination.

Source table:

CustID     Value
1   20.00
1   50.00
1   50.00
2   30.00
2   50.00
2   50.00

Should return this please ( change when either of the columns change)

Nr  CustID     Value
1   1   20.00
2   1   50.00
2   1   50.00
3   2   30.00
4   2   50.00
4   2   50.00

Thus there are 4 unique combinations of Customer/Value and thus 4 nrs.

SQL:

declare @table table(
CustomerID int,
Value       decimal(18,2)
)


insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,20)
insert Into @table(CustomerID,Value)values(2,30)

select * from @table


select  RANK()   OVER(PARTITION BY CustomerID,Value ORDER BY  Value  DESC) as Rank ,
* from @table 

http://www.sqlfiddle.com/#!3/7e60e/3

  • perfect thanks, all the examples had something in the Partition By section which I should have left out. – Peter PitLock Jun 20 '14 at 11:10
2

You need to use:

DENSE_RANK() OVER (ORDER BY CustomerID, Value)

Test at SQL-Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.