CODEWITHSUNDEEP
sqlsql-server
Joseph
Joseph

Reputation: 570

Partition by multiple columns having duplicate values in MS SQL

As an example, I have two columns in a table 

**Fruit Color**
Mango Yellow
Mango Yellow
Apple Red
Apple Red

Expected Output

**Rank Fruit Color**
1    Mango Yellow
1    Mango Yellow
2    Apple Red
2    Apple Red

Tried Row_number() but it doesn't seem to yield the expected output. Tried Rank() and Dense_Rank() as well but didn't get the expected outcome

SELECT ROW_NUMBER() OVER (PARTITION BY Fruit,Color order by Fruit) , Fruit,Color
from #temp
ORDER BY FRUIT

rwno    Fruit   Color
1   Apple   Red
2   Apple   Red
1   Mango   Yellow
2   Mango   Yellow

Upvotes: 0

Views: 119

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You want dense_rank() and no partition by:

SELECT DENSE_RANK() OVER (ORDER BY Fruit, Color), Fruit, Color
from #temp
ORDER BY FRUIT

Upvotes: 2

Related Questions