select only max values per group sql-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

prueba usar distinct en lugar de group by para agrupar el campo "key". El distinct se usa en el select. SELECT DISTINCT KEY, MAX(SALES), COD FROM ...