Using SQL to get the last (or first) few rows with a given filter

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

Leave a Reply