user3769402 user3769402 - 7 months ago 14
SQL Question

Adding an extra SQL select statement is returning too many results

I have 3 tables that all need to interact. They are for a bike inventory. They are bike shop, shop inventory, and bike model. I want to find the cheapest bike in each shop inventory. The code below gives me the correct result I expect, but I also want to show the bike name.

SELECT
BS.BIKESHOPNAME, MIN(BM.PRICE)
FROM
BIKESHOP BS, BIKEMODEL BM, SHOPINVENTORY SI
WHERE
SI.BIKESHOPID = BS.BIKESHOPID
AND SI.BIKEID = BM.BIKEID
GROUP BY
BS.BIKESHOPNAME;


If I change the select statement to look like:

SELECT BS.BIKESHOPNAME, BM.NAME, MIN(BM.PRICE)


I get too many results. Do I have to check that the name matches the bike model?

Answer

I think that this should solve your problem:

SELECT DISTINCT
BS.BIKESHOPNAME,
FIRST_VALUE(BM.NAME) OVER (PARTITION BY BS.BIKESHOPNAME ORDER BY BM.PRICE ASC),
FIRST_VALUE(BM.PRICE) OVER (PARTITION BY BS.BIKESHOPNAME ORDER BY BM.PRICE ASC)
FROM BIKESHOP BS, BIKEMODEL BM, SHOPINVENTORY SI
WHERE SI.BIKESHOPID = BS.BIKESHOPID AND SI.BIKEID = BM.BIKEID

I'm not sure that this is the best solution but i think it will do. It can also be solved using sub-queries but I think this one is better.

Comments