Better solution for Question no 1.16 in 1_questions_and_solutions.sql (SQL_exercise_01).

[RohitDahiyardx]

I have a better solution for Question 1.16 in SQL_exercise_01 which can solve the query in just 2 lines.

My solution:

select b.name, a.price, a.name
from products a join manufacturers b on (a.Manufacturer= b.code )
where a.price in (select max(price) as p from products group by manufacturer order by max(price) );

Current solution:

select max_price_mapping.name as manu_name, max_price_mapping.price, products_with_manu_name.name as product_name
from
(SELECT Manufacturers.Name, MAX(Price) price
FROM Products, Manufacturers
WHERE Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name)
as max_price_mapping
left join
(select products.*, manufacturers.name manu_name
from products join manufacturers
on (products.manufacturer = manufacturers.code))
as products_with_manu_name
on
(max_price_mapping.name = products_with_manu_name.manu_name
and
max_price_mapping.price = products_with_manu_name.price);

[PD013]

SELECT M.name , P.name , P.price
FROM products P
INNER JOIN manufacturers M
ON M.code = P.manufacturer
AND P.Price = (
SELECT MAX(P.Price)
FROM products P
WHERE P.Manufacturer = M.Code);

Can also be written like this