|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE #temp ( Salesperson VARCHAR(10), Customer_Name VARCHAR(10), Total_Sales INT ) INSERT INTO #temp VALUES ('Amy', 'John', 100000), ('Amy', 'Jack', 50000), ('Amy', 'Janice', 870000), ('Jack', 'Amy', 5000), ('Jack', 'Bob', 3000), ('Jack', 'Susan', 500000), ('Jack', 'Sammy', 500000), ('Jack', 'Maurice', 300000), ('Tom', 'Amy', 200000), ('Tom', 'Ricky', 200000), ('Tom', 'Sam',50000), ('Tom', 'Greg', 3000) SELECT * FROM #temp |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Salesperson Customer_Name Total_Sales Amy John 100000 Amy Jack 50000 Amy Janice 870000 Jack Amy 5000 Jack Bob 3000 Jack Susan 500000 Jack Sammy 500000 Jack Maurice 300000 Tom Amy 200000 Tom Ricky 200000 Tom Sam 50000 Tom Greg 3000 |
As the names suggest, they can be used to rank. They are useful for a use case such as finding the top customer or second, or third customer of each salesperson.
RANK
|
1 2 3 |
SELECT *, RANK() OVER (PARTITION BY Salesperson ORDER BY Total_Sales DESC) AS rank FROM #temp |
|
1 2 3 4 5 6 7 8 9 |
Salesperson Customer_Name Total_Sales rank Amy Janice 870000 1 Amy John 100000 2 Amy Jack 50000 3 Jack Susan 500000 1 Jack Sammy 500000 1 Jack Maurice 300000 3 Jack Amy 5000 4 Jack Bob 3000 5 |
In case of Jack, we can see that RANK returned 1, 1, and 3. But that probably should have been two? If that’s the case…
DENSE_RANK
|
1 2 3 |
SELECT *, DENSE_RANK() OVER (PARTITION BY Salesperson ORDER BY Total_Sales DESC) AS rank FROM #temp |
|
1 2 3 4 5 6 7 8 9 |
Salesperson Customer_Name Total_Sales rank Amy Janice 870000 1 Amy John 100000 2 Amy Jack 50000 3 Jack Susan 500000 1 Jack Sammy 500000 1 Jack Maurice 300000 2 Jack Amy 5000 3 Jack Bob 3000 4 |
So, if we wanted to see the customer with highest sales.
|
1 2 3 4 5 6 |
SELECT * FROM ( SELECT *, DENSE_RANK() OVER (PARTITION BY Salesperson ORDER BY Total_Sales DESC) AS rank FROM #temp) AS tmp WHERE tmp.rank = 1 |
|
1 2 3 4 5 6 |
Salesperson Customer_Name Total_Sales rank Amy Janice 870000 1 Jack Susan 500000 1 Jack Sammy 500000 1 Tom Amy 200000 1 Tom Ricky 200000 1 |