Good Uses for SQL Server 2005 Common Table Expressions (Part 1)

Previous posts in this series:

• Part 0 - Introduction

Determining the top and bottom n pieces of information

in a set

This scenario involves a set that has a column with both positive and negative ranges of data. The requirement is to filter it such that the only rows returned are those with the 3 highest positive and 3 lowest negative values - basically the two extremes.

The sample data

The data I’ll be working with in this example is in a table called SampleData, which contains:

Step 1: Get the data in the right order

Before we touch a CTE, we need to work out how we can get the data in priority order, that is to say, the most positive and most negative numbers at the top of the list. This isn’t as simple as just ordering by Value in ascending or descending order, because whichever way we order, either the negative or positive numbers will be going in the wrong direction. A nice solution to this is to order by the absolute value, like this:

1SELECT [Key], [Value]
2FROM SampleData
3ORDER BYABS([Value])DESC

Which returns the data as:

As you can see, although the positive and negative numbers are all mixed together, they are in the right order - from lowest negatives up and highest positives down.

Step 2: Introduce the CTE magic

There are two things we need to do to the data now it’s ordered, somehow segregate the positive and negative numbers and select the most appropriate 3 records of each.

To start things we’ll use the ROW_NUMBER() ranking function to provide a rank to each row, the first row being assigned 1, then next 2 and so on:

 1WITH OrderedData AS
 2(
 3 SELECT [Key],
 4        [Value],
 5     ROW_NUMBER()OVER(
 6         ORDER BYABS([Value])DESC)AS RankPosition
 7    FROM SampleData
 8)
 9SELECT [Key], [Value], [RankPosition]
10FROM OrderedData

The ORDER BY clause has moved from the select statement and is now used to specify the order the records should be ranked, and the main select statement forms part of a CTE. Running this gives us the following:

But what we want is the positive numbers and negative numbers numbered independently. To do this we can use the windowing clause PARTITION BY in the ranking statement:

 1WITH OrderedData AS
 2(
 3 SELECT [Key],
 4        [Value],
 5     ROW_NUMBER()OVER(
 6         PARTITION BYSIGN([Value])ORDER BYABS([Value])DESC)AS RankPosition
 7    FROM SampleData
 8)
 9SELECT [Key], [Value], [RankPosition]
10FROM OrderedData

Here we are partitioning by the sign of the Value data, which means we now get:

The only thing left to do is make sure that only up to 3 rows from the positive and negative sets are returned - this is a really simple addition:

 1WITH OrderedData AS
 2(
 3 SELECT [Key],
 4        [Value],
 5     ROW_NUMBER()OVER(
 6         PARTITION BYSIGN([Value]) 
 7         ORDER BYABS([Value])DESC)AS RankPosition
 8    FROM SampleData
 9)
10SELECT [Key], [Value]
11FROM OrderedData
12WHERERankPosition<=3

Which gives us exactly what we were after: