Ilya Skaba Ilya Skaba - 4 months ago 8
SQL Question

Select max value from column, except have specific value in other column

I have the following table, describing some plants

p_id p_type p_name p_size p_tasty
1 veggie potato 1 0
2 veggie carrot 2 0
3 veggie cabbage 3 0
4 fruit strawberry 1 1
5 fruit apple 2 0
6 fruit watermelon 3 0


if i want to get the largest plant of every type, the query will be like this:

SELECT a.p_id,a.p_type, a.p_name,a.p_size,a.p_tasty
FROM test.plants a
INNER JOIN (
SELECT b.p_type, MAX(b.p_size) p_size
FROM test.plants b
GROUP BY b.p_type
) b ON a.p_type = b.p_type AND a.p_size = b.p_size


and will give me this:

p_id p_type p_name p_size p_tasty
3 veggie cabbage 3 0
6 fruit watermelon 3 0


But how can i get the largest OR the tastiest plant of every type?

i.e., select plant that has p_tasty value of 1, and if there is no such plant of this type, select the largest one.

I think i could use
case when exists then ...
clause, but maybe there is simpler way to do this?

Answer

LEFT JOIN your query with the tasty plants, and use the largest as a default if there's no tasty plant.

SELECT IFNULL(c.p_id, a.p_id) AS p_id, a.p_type, IFNULL(c.p_name, a.p_name) AS p_name, IFNULL(c.p_size, a.p_size) AS p_size, IFNULL(c.p_tasty, a.p_tasty) AS p_tasty
FROM test.plants a
INNER JOIN (
    SELECT b.p_type, MAX(b.p_size) p_size
    FROM test.plants b
    GROUP BY b.p_type
) b ON a.p_type = b.p_type AND a.p_size = b.p_size
LEFT JOIN test.plants c ON a.p_type = c.p_type AND c.tasty = 1