SQL Server - Get Nth Highest using ROW_NUMBER() Function

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

USE [Northwind]
GO
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]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetHighestSales_sp
(
@Rank int = NULL,
@FROM int = 1,
@TO int = 1
)
AS
BEGIN
--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,
OrderTotal.SalesRank
FROM
Customers C
INNER JOIN
(
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
FROM
[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
SELECT
OrderID, E.LastName + ', ' + E.FirstName AS EmployeeName, ContactName,
CompanyName, Phone, TotalPurchasePrice, SalesRank
FROM
Employees E
INNER JOIN CustomerOrders CO ON CO.EmployeeID = E.EmployeeID
WHERE
CO.SalesRank between @From AND @To

--reset
SET ROWCOUNT 0
END


(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.