How to pick a value associated with the maximum value in a table [WITHOUT USING LIMITS]

Question

I have a table like this that contains values like

Sales Table

| Year | Profit       | 
|------|--------------|
| 1982 | 20           |
| 2000 | 100          |
| 1900 | 10           | 
| 1800 | 2            |

I basically want to pick the year that has the maximum profit i.e. year 2000 because the profit there is 100.

What I tried so far:

SELECT year FROM Sales WHERE max(profit)

Beginner in SQL, sorry if the question is too simple.

WITHOUT USING LIMITS.

Would you care to share why you don't want to use `LIMIT`? – Rick James Feb 21 '20 at 05:58 — Rick James, Feb 21 '20 at 05:58

O. Jones · Answer 1 · 2020-02-20T21:42:50.523

Darn it, I know your question has been answered on here, but I can't find it. So here goes.

LIMITS wouldn't help with this at all.

You're pretty close already. This is what you want.

SELECT year FROM Sales HAVING Profit = MAX(profit)

A good way to explain this HAVING column = FUNC(column) sorcery is to refactor the query like this.

SELECT Profit, Year
  FROM Sales
 WHERE Profit = (SELECT MAX(Profit) FROM Sales)

You first scan the table to find the highest Profit. You then find the row (or beware, rows in case of a tie) that have that value.

score 0 · Answer 2 · answered Feb 20 '20 at 21:32

0

SELECT year FROM
(SELECT year, max(profit) FROM Sales GROUP BY year) a

answered Feb 20 '20 at 21:32

Popeye

321
3
12

1

I think that is equivalent to `SELECT DISTINCT year FROM Sales;` – Rick James Feb 21 '20 at 05:53
I'm amazed at how many Answerers are so focused on addressing the Question that they fail to make a simplification pass over their Answer. – Rick James Feb 21 '20 at 18:55

score 0 · Answer 3 · answered Feb 20 '20 at 23:56

@O.Jones is spot on with the remark about tie values.

Those can easily lead to unexpected results, especially returning multiple result rows for a year.

One way to avoid this is to be very clear about what record should be returned, i.e. you may want the first entry of every year when sorted by profit. The next entries might have the same profit value, but should not be returned.

In SQL this can look like this:

WITH _profits_in_years  
(YEAR, profit)   as
    (SELECT 1999 AS YEAR, 200 AS Profit FROM dummy UNION ALL
     SELECT 1999 AS YEAR, 50  AS Profit FROM dummy UNION ALL
     SELECT 2000 AS YEAR, 100 AS Profit FROM dummy UNION ALL
     SELECT 2001 AS YEAR, 100 AS Profit FROM dummy UNION ALL
     SELECT 2002 AS YEAR, 200 AS Profit FROM dummy UNION ALL
     SELECT 2002 AS YEAR, 200 AS Profit FROM dummy UNION ALL
     SELECT 2003 AS YEAR, NULL AS Profit FROM dummy  
    )
SELECT DISTINCT
    YEAR, 
    FIRST_VALUE(Profit) OVER 
        (PARTITION BY YEAR
         ORDER BY PROFIT DESC NULLS LAST)
FROM
    _profits_in_years

How to pick a value associated with the maximum value in a table [WITHOUT USING LIMITS]

3 Answers3