It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

My CTE

When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
FROM Production.Product p
INNER JOIN ( SELECT  ph.ProductID, ph.StandardCost, pri.Quantity
FROM Production.ProductCostHistory ph
INNER JOIN Production.ProductInventory pri
ON ph.ProductID = pri.ProductID
WHERE StandardCost > 10
) b
ON p.ProductID = b.ProductID
INNER JOIN Production.ProductInventory pi
ON  p.ProductID = pi.ProductID
WHERE p.Color IS NULL
  AND p.DiscontinuedDate IS NULL

A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

WITH ProductCTE
AS
(
SELECT  ph.ProductID, ph.StandardCost, pri.Quantity
FROM Production.ProductCostHistory ph
INNER JOIN Production.ProductInventory pri
ON ph.ProductID = pri.ProductID
WHERE StandardCost > 10
)
SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
FROM Production.Product p
INNER JOIN ProductCTE b
ON p.ProductID = b.ProductID
INNER JOIN Production.ProductInventory pi
ON  p.ProductID = pi.ProductID
WHERE p.Color IS NULL
  AND p.DiscontinuedDate IS NULL

I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

Top X of a Group

Suppose you have a small result set of something like this.

CREATE TABLE Books
( BookID INT IDENTITY(1,1)
, BookName VARCHAR(200)
, Genre VARCHAR(50)
, reads INT
)
go
INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
INSERT books SELECT 'The Enemy', 'Thriller', 67
INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
INSERT books SELECT 'Game of Lions', 'History', 644
INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

WITH BookRanks AS
(
SELECT b.BookID
, b.BookName
, b.Genre
, b.reads
, ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter
  FROM Books b
)
SELECT bookID
, Genre
, reads
, bookname
from BookRanks
WHERE counter <= 2

That gives me an easy to read result set:

bookID  Genre    reads bookname

——- ——– —– ———————————————————-

8       History  644   Game of Lions

10      History  267   An American Life: The Autobiography

2       Sci-Fi   345   Ender’s Game

1       Sci-Fi   200   Old Man’s War

6       Thriller 678   The Hunt for Red October

7       Thriller 545   Bad Luck and Trouble

Older T-SQL Tuesday Topics

Just a quick list of the past topics and the roundups.

• T-SQL Tuesday #001: Date/Time Tricks
• T-SQL Tuesday #002: A Puzzling Situation
• T-SQL Tuesday #003: Relationships
• T-SQL Tuesday #004: IO
• T-SQL Tuesday #005- Reporting
• T-SQL Tuesday #006: “What About BLOB?”
• T-SQL Tuesday #007: Summertime in the SQL
• T-SQL Tuesday #008: Gettin’ Schooled
• T-SQL Tuesday #009: Beach Time
• T-SQL Tuesday #010 – Indexes
• T-SQL Tuesday #011: Misconceptions
• T-SQL Tuesday #012 – Why are DBA skills necessary?
• T-SQL Tuesday #013 – What the Business Says is Not What the Business Wants (Summary)
• T-SQL Tuesday #014 – Resolutions (roundup)
• T-SQL Tuesday #015 – Automation (roundup)
• T-SQL Tuesday #016 – Apply (roundup)
• T-SQL Tuesday #017 – Automation (roundup)

Filed under: Blog Tagged: syndicated, T-SQL, T-SQL Tuesday