CODEWITHSUNDEEP
mysqlsqldatabase
programmer001
programmer001

Reputation: 69

Using subqueries to show multiple answers which have this same value

I want to show this products which has the highest prize (group by manufacturer). But if two products has this same prize, SQL shows me one of them. 

SELECT name, manufacturer, MAX(prize)
FROM products p
GROUP BY manufacturer
HAVING MAX(prize) = (SELECT MAX(p1.prize)
                  FROM products p1
                  WHERE p.manufacturer = p1.manufacturer
                  LIMIT 1
                 );

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You seem to want a simple subquery -- no aggregation in the outer query:

SELECT p.*
FROM products p
WHERE p.prize = (SELECT MAX(p2.prize)
                 FROM products p2
                 WHERE p2.manufacturer = p.manufacturer
                );

As mentioned in your previous question, your use of GROUP BY is not correct. You have unaggregated columns in the SELECT that are not in the GROUP BY.

Your use of aggregation in the outer query is why you are getting only one row per manufacturer.

In terms of performance, this is often the fastest method -- with the right indexes. In this case, the right index is on (manufacturer, prize).

Upvotes: 1

forpas
forpas

Reputation: 164139

First select all the max prizes group by maufacturer and join to the main table:

select p.* from
(select manufacturer, MAX(prize) AS maxprize from products GROUP BY manufacturer) AS m
inner join products p
on m.manufacturer = p.manufacturer and m.maxprize = p.prize
order by p.manufacturer, p.name

See demo

Upvotes: 1

Related Questions