SQL/ Return MIN values of multiple rows

I’m trying to get the minimum value of open, across multiple rows of year. This is from app.mode.com and the site only says SQL, not sure which version

SELECT year,
    open
  FROM tutorial.aapl_historical_stock_price
WHERE open = (select MIN(open)
  FROM tutorial.aapl_historical_stock_price)

When I use the code above, the result is
Table result vs actual output
| Year | Open |
|—— |——|
|2000 | 0 |
|2000 | 0 |
|2000 | 0 |

What I’m trying to get is
| Year | Open |
|—— |——|
|2002 | 0 |
|2001 | 0 |
|2000 | 0 |
Can someone help point me what I’m doing wrong?

>Solution :

select year and get the min by grouping each year as following:

select 
    year
    , min(open) as <desired_alias>
from your_table
group by 1
order by 1 desc;

Leave a Reply