Smaller entry today. Wrote some cool SQL that I just had to share, but didn’t feel like it needed to be wrapped in a lager entry.
TSQL makes it easy to ORDER BY and SELECT TOP when there is a set of data well defined by your schema. Given a table:
--------------------------------------------- ID | Manufacturer | Model | UnitsSold --------------------------------------------- 1 | Ferrari | 599 GTO | 392 2 | Ferrari | Enzo | 34 3 | Ferrari | Testarossa | 218 4 | Ferrari | Dino | 87 5 | Porsche | 911 | 1302 6 | Porsche | Boxster | 1511 7 | Porsche | 928 | 819 ---------------------------------------------
DECLARE @CarTable TABLE
(
ID INT,
Manufacturer VARCHAR(128),
Model VARCHAR(128),
UnitsSold INT
)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (1,'Ferrari','599 GTO',392)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (2,'Ferrari','Enzo',340)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (3,'Ferrari','Testarossa',218)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (4,'Ferrari','Dino',87)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (5,'Porsche','911',1302)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (6,'Porsche','Boxster',1511)
INSERT INTO @CarTable (ID, Manufacturer, Model, UnitsSold) VALUES (7,'Porsche','928',819)
Getting the top 2 selling models of cars is a fairly straight forward SQL query.
SELECT TOP 2
Manufacturer,
Model,
UnitsSold
FROM @CarTable
ORDER BY UnitsSold DESC
Things get more complicated if we wanted to get the top 2 selling model of cars per manufacturer. The trick is to use the RANK function.
SELECT
Manufacturer,
Model,
UnitsSold
FROM
(
SELECT
Manufacturer,
Model,
UnitsSold,
Rank = RANK() OVER (PARTITION BY Manufacturer ORDER BY UnitsSold DESC)
FROM @CarTable
) cars
WHERE cars.[Rank] <= 2 -- Get top 2 from each Manufacturer
ORDER BY Manufacturer
