Friday, March 25, 2011

How to select the row with max/min value in each group in SQL

For example we have a Cars table:
Cars(CategoryID,Model,Price)
Let's say we want to select the cheapest car in each category,
First we need to group the cars by CategoryID and choose the min price,
Then, select the rest of the row by joining that result back to the same table (self join)
SELECT c.CategoryID, c.Model, c.Price
FROM
(
   SELECT CategoryID, MIN(Price) as MinPrice
   FROM Cars 
   GROUP BY CategoryID
) AS x
INNER JOIN Cars AS c 
ON c.CategoryID = x.CategoryID AND c.Price = x.MinPrice

Select the top 2 rows from each group
SELECT CategoryID, Model, Price
FROM Cars
WHERE
(
   SELECT COUNT(*) FROM Cars as c
   where c.CategoryID = Cars.CategoryID AND c.Price < Cars.Price
) <= 2

OR something like this
(SELECT * from Cars WHERE CategoryID = 1 ORDER BY Price LIMIT 2)
UNION ALL
(SELECT * from Cars WHERE CategoryID = 2 ORDER BY Price LIMIT 2)
UNION ALL
(SELECT * from Cars WHERE CategoryID = 3 ORDER BY Price LIMIT 2)

1 comments:

kenzie jones said...

Nice post. That is the real way to explain anything. I like it. You explained all the necessary information in brief and simple way. Thanks.
digital certificates

Post a Comment