About a month ago I found the SQLShare site which provides useful, clear tutorial videos of how to use some SQL functions, or how to fine tune a query. Their videos are roughly 3-5 minutes long and have proved to be very good for me with a strong BI background with less first-hand T-SQL experience.
I decided to make notes of the ones I watched and found useful and instead of putting them into a word document somewhere locally I'll publish them on this blog so. These would be very simple and short nuggets.
For today: ranking with ROW_NUMBER
The ROW_NUMBER function allows you to assign a ranking number to the rows of a table based on one or multiple conditions. E.g. if you want to create a rank based on the LineTotal column of AdventureWorks.Sales.SalesOrderDetail and put the ranking number into a new column just execute the following command:
SELECT
ROW_NUMBER () OVER(ORDER BY LineTotal DESC) AS ProfitRank,
*
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
Since there are multiple rows with the same LineTotal you might need to include other conditions, like Order Quantity:
SELECT
ROW_NUMBER () OVER(ORDER BY LineTotal DESC, OrderQty DESC) AS ProfitRank,
*
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
The other useful argument for ROW_NUMBER is PARTITION BY. Let's see the following query:
SELECT
ROW_NUMBER () OVER(PARTITION BY ProductID ORDER BY LineTotal DESC) AS ProfitRank_by_Product,
*
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
You can see the this time the ranking is restarted when ProductID changes so you can check easily which was the best line for each Product:
SELECT ProductID, LineTotal, ProfitRank_by_Product
FROM
(
SELECT
ROW_NUMBER () OVER(PARTITION BY ProductID ORDER BY LineTotal DESC) AS ProfitRank_by_Product,
ProductID, LineTotal
FROM [AdventureWorks].[Sales]
.[SalesOrderDetail]
) Sales
WHERE ProfitRank_by_Product = 1
That's all for today. Simple, basic but clear I hope :)