T-SQL bits - ROW_NUMBER

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 :)

Published Friday, June 4, 2010 12:03 AM by MartinIsti
Filed under: ,

Comments

No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems