select only max values per group sql

November 12, 2022

I have the following table

Sales Key Cod  Value
10    A   109  10.99  
7     A   10   11.05
3     B   1    10.30
25    B   125  15.30

How can I get only the max(sales) value per key, like:

Sales Key Cod  Value
10    A   109  10.99  
25    B   125  15.30

I tried:

SELECT MAX(sales), key, cod, value FROM table GROUP BY key

But i got the following error

expression 'cod' is neither present in the group by, nor is it an aggregate function

When I put cod in group by, then I got the two values, not only the max.

I know I can solve this problem using Window Function, but I think it’s not the efficient way to solve this. Any thoughs on that?

>Solution :

There’s nothing wrong with using a window function. But if you dislike that, you can also just use a subquery to avoid your GROUP BY issue:

SELECT 
sales,
column1,
cod,
column2
FROM yourtable y1
WHERE sales =
(SELECT MAX(y2.sales)
FROM yourtable y2
WHERE y1.column1 = y2.column1)
ORDER BY column1;

Note: Renamed your columns "key" and "value" in my query to "column1" and "column2" because SQL key words should not be used as column name or table name.