GROUP BY clause in SQL-CodePudding

Year    Brand   Amount
2018    Apple   45000
2019    Apple   35000
2020    Apple   75000
2018    Samsung 15000
2019    Samsung 20000
2020    Samsung 95000
2018    Nokia   21000
2019    Nokia   17000
2020    Nokia   14000

i want the expexted output to be like:

Year     Brand       Amount
2018     Apple       45000
2019     Apple       35000
2020     Samsung     95000

THIS IS WHAT I TRIED:

Select Year, Brand, Max(Amount)as HighestPrice
from Practice
Group by Year

but it shows error:

"Column 'Practice.Brand' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I would highly appreciate the help. Thanks

CodePudding user response：

The error is happening because you need both in the group by. I would just write a standard select and join into the max amount in a sub query and correlate on year.

SELECT  YEAR
      ,BRAND
      ,Amount AS HighestPrice
  FROM Practice B
  WHERE Amount = (SELECT MAX(Amount) FROM Practice A
                                    WHERE A.YEAR = B.YEAR) 

 ORDER BY YEAR ASC

CodePudding user response：

A generic SQL version would be:

select p.Year, p.Brand, p.Amount as HighestPrice
from Practice p
  join (
    select Year, max(Amount) as Amount
    from Practice
    group by Year
  ) as m on m.Amount=p.Amount

CodePudding user response：

You don't say which database you are using, so I'll assume it's PostgreSQL. You can use DISTINCT ON to get the row with the max amount per year.

For example:

select distinct on (year) * from practice order by year, amount desc

CodePudding user response：

You can use partition, example in ssms:

SELECT Year, Brand, Amount as HighestPrice
FROM(
  SELECT
  Year, Brand, Amount,
   ROW_NUMBER()OVER (PARTITION BY year ORDER BY Amount DESC) AS rn
  FROM Practice
  ) a
WHERE rn = 1 
ORDER BY year