Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

T-SQL Tuesday #18 – My CTE

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.


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

Comments

Posted by Jason Brimhall on 10 May 2011

Steve, Nice submission.

#17 should be "Apply" instead of "Automation"

Posted by Steve Jones on 10 May 2011

Thanks, changed

Leave a Comment

Please register or log in to leave a comment.