* Compatibility SQL Server 2005 onwards
ROW_NUMBER function was introduced in SQL Server 2005. ROW_NUMBER as the name suggests is used to calculate row numbers in the query result set.
Prior to ROW_NUMBER developers relied on temporary tables and co-related sub-queries to calculate sequential output, this often resulted in spaghetti code and overall bad query performance.
For more details on ROW_NUMBER visit: MSDN
Let us consider a real-world ROW_NUMBER example using NorthWind database. You can download and install NorthWind database from the following location.
Consider this scenario, we want to run a report which gives us Top Order by sales.
We are not calculating ties here i.e. what if more than one order generated exactly same sales amount, ties can be handled using using RANK() function but for now we will assume sequential result set.
The stored procedure which we are going to see will provide the flexibility to return exact result set i.e. Order with highest sales or range i.e. Top 10 Orders by sales
Stored procedure code:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetHighestSales_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetHighestSales_sp]
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE dbo.GetHighestSales_sp
@Rank int = NULL,
@FROM int = 1,
@TO int = 1
--no extra columns
SET NOCOUNT ON;
/*CTE Declaration block */
WITH CustomerOrders (OrderID, CustomerID, EmployeeID, ContactName, CompanyName, Phone, TotalPurchasePrice, SalesRank) AS
SELECT OrderTotal.OrderID As OrderID, C.CustomerID As CustomerID, E.EmployeeID As EmployeeID,
C.ContactName, C.CompanyName, C.Phone, OrderTotal.TotalPurchasePrice,
SELECT ORD.OrderID As OrderID, O.CustomerID, O.EmployeeID,
Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money)) AS TotalPurchasePrice,
Row_Number() OVER(ORDER BY (Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money))) DESC) AS SalesRank
[Order Details] ORD
INNER JOIN ORDERS O ON O.OrderID = ORD.OrderID
GROUP BY ORD.ORDERID, O.CustomerID, O.EmployeeID
) AS OrderTotal ON OrderTotal.CustomerID = C.CustomerID
INNER JOIN Employees E ON OrderTotal.EmployeeID = E.EmployeeID
--determine which result to extract
OrderID, E.LastName + ', ' + E.FirstName AS EmployeeName, ContactName,
CompanyName, Phone, TotalPurchasePrice, SalesRank
INNER JOIN CustomerOrders CO ON CO.EmployeeID = E.EmployeeID
CO.SalesRank between @From AND @To
SET ROWCOUNT 0
(a) Top Order by sales
EXEC dbo.GetHighestSales_sp 1
Query returns Order with highest sales amount.
(b) Top 10 Order by sales
EXEC dbo.GetHighestSales_sp NULL, 1, 10
Query returns Top 10 Orders by sales amount.