0

I am practicing on SQL and I am having this problem

[** From the following tables write a SQL query to find the most expensive product of each company. Return pro_name, and com_name**] [1]: https://i.stack.imgur.com/jtJ85.png

this problem can be solved using a sub query on the WHERE clause, however am trying to solve this problem using GROUP BY, is there a way?

I tried solving it using this code

*

SELECT COM_NAME,PRO_NAME, MAX(PRO_PRICE)
FROM company_mast C
JOIN item_mast I
ON C.COM_ID=I.PRO_COM
GROUP BY COM_NAME,PRO_NAME

I realized that the group by is done on the PRO_NAME not the COM_NAME which I think is because PRO_NAME has more elements then COM_NAME am I right? and is there a way to solve it using GROUP BY

anis smail
  • 1
  • 2
  • Solving this with `GROUP BY` alone would be very tricky. First of all you want one result row per company, which translates to `GROUP BY com_name`. Then you can select the `MAX(price)`. But how to get the pro_name that belongs to that maximum price then? There is no aggregation function saying "give me the product name for the maximum price" in MySQL. (There is one in Oracle, but this is a propriatary function that you don't find in other DBMS.) The typical ways to solve this kind of probelm is either with a subquery or with a window function (e.g. MAX() OVER ()`). – Thorsten Kettner Sep 02 '22 at 04:48
  • And then, with top n questions (you want the top one product per company), you must always decide (or have it decided) how to deal with ties. What exactly do you want to do, if a company has two or more top products at the same price? – Thorsten Kettner Sep 02 '22 at 04:50

0 Answers0