How to a value from comparing two MAX() columns in SQL-CodePudding

I have the following table:

| beverage | country | units_a_sold | units_b_sold |
| -------- | ------- | ------------ | ------------ |
| fanta    | US      | 183          |          209 |
| fanta    | DE      | 354          |          107 |

I would like to get the country where the max units were sold, from either a or b. So in this case it would be: | fanta | DE |

This is what I have so far:

SELECT beverage
    , country
    , CASE
        WHEN MAX(units_a_sold) > MAX(units_b_sold) THEN (...)
FROM table

CodePudding user response：

We can do a limit query with the help of the scalar GREATEST function:

SELECT *
FROM yourTable
ORDER BY GREATEST(units_a_sold, units_b_sold) DESC
LIMIT 1;

If there could be two or more countries tied with the most sold, then we can use RANK():

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY GREATEST(units_a_sold, units_b_sold) DESC) rnk
    FROM yourTable
)

SELECT beverage, country
FROM cte
WHERE rnk = 1;