Find high and low prices in sql

Question

In this sample database there are two tables, products and prices. The goal is to find the highest and the lowest price for each product.

The price table can have either zero, one or two rows per product.

create table products(
    id int,
    name nvarchar(50)
)

create table prices(
    productId int,
    price int
)

insert into products (id, name) values (33,'bike')
insert into products (id, name) values (44,'car')
insert into products (id, name) values (55,'bus')

insert into prices (productId, price) values (33, 10)
insert into prices (productId, price) values (33, 40)
insert into prices (productId, price) values (44, 300)

The sql query should result in this:

productId  highPrice  lowPrice
33         40         10
44         300        NULL
55         NULL       NULL

Answer 1

SELECT productId,
        MAX(price) AS highPrice,
        MIN(price) AS lowPrice
FROM prices
GROUP BY productId

and if you want the product name in there as well:

SELECT name,
        MAX(price) AS highPrice,
        MIN(price) AS lowPrice
FROM products
    LEFT OUTER JOIN prices ON ID = ProductID
GROUP BY name

Answer 2

This is for MySQL, but it might work for you too.

SELECT
products.id as productId
, MIN(price) as highPrice
, MAX(price) as lowPrice
FROM products
  LEFT JOIN prices ON products.id=prices.productId
GROUP BY products.id

Answer 3

This gives you the table that you're looking for (I notice that the other answers don't), in SQL Server 2005

select P.ID as ProductID, 
nullif(sum(case when idx=1 then price else 0 end), 0) as highPrice,
nullif(sum(case when idx=2 then price else 0 end), 0) as lowPrice  from
(
    select productid, price, row_number() over(partition by productID order by price desc) as idx from prices
) T
right join products P on T.productID = P.ID
group by P.ID