Published on

July 11, 2010

Using Common Table Expressions and Row_number() in SQL Server

SQL Server 2005 introduced two powerful features: Common Table Expressions (CTEs) and the Row_number() function. In this article, we will explore how these two features can be combined to solve a common problem in a more elegant way.

Common Table Expressions

A CTE is a temporary result set that is valid for the scope of a single Select, Update, Insert, or Delete statement. While some argue that the limited scope of CTEs restricts their usefulness, they offer more than just improved SQL readability. One key advantage is that the result set can be referenced multiple times within the same statement.

Row_number()

The Row_number() function is a long-awaited addition to SQL Server. It assigns a sequential row number to each record in a result set. You can specify an ordering to determine the basis for the numbering, and optionally partition the result set to create separate numbering sequences.

The Price History Example

Let’s consider a scenario where a retail client wants to generate a report showing the item name, old price, new price, and the date range for which the new price was applied. Traditionally, this type of question has been challenging for SQL developers, often requiring messy subqueries or storing both start and end dates in the table. However, we can leverage CTEs and the Row_number() function to provide a more elegant solution.

The Solution

The first step is to construct a CTE that numbers the rows of the data table. We partition the numbering by the item ID and order it by the price start date. This allows us to link each row to its previous and next rows.

;WITH PriceCompare AS (
    SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
    ROW_NUMBER() OVER (PARTITION BY ph.ItemId ORDER BY PriceStartDate) AS rownum 
    FROM Items i 
    INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemId
) 
SELECT * FROM PriceCompare

Next, we enhance the SELECT statement by joining the CTE with itself to retrieve the previous and next rows for each item. We use left joins to handle the first and last rows, which have no previous or next rows.

SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate 
FROM PriceCompare currow 
LEFT JOIN PriceCompare nextrow 
    ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId 
LEFT JOIN PriceCompare prevrow
    ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId

By running this query, we obtain the desired result set for the Price History report. Additionally, we can encapsulate the entire CTE in a view for easier reuse.

CREATE VIEW [dbo].[PriceCompare] AS
WITH PriceCompare AS 
(
    SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price, 
    ROW_NUMBER() OVER (PARTITION BY ph.ItemId ORDER BY PriceStartDate) AS rownum 
    FROM Items i 
    INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemId
)
SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate 
FROM PriceCompare currow 
LEFT JOIN PriceCompare nextrow 
    ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId 
LEFT JOIN PriceCompare prevrow 
    ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId

In conclusion, CTEs and the Row_number() function offer a powerful combination for solving complex problems in SQL Server. By leveraging these features, you can achieve more elegant and efficient solutions, improving both the readability and performance of your SQL code.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.