sql

SQL conditional MAX extraction

From a complex DB (and using multiple joins), I'm currently able to extract an output like this:

ID VALUE
001 25
001 12
001 20
002 9
002 38
003 0
003 41
003 25
003 7

But what I would like to achieve is:

ID VALUE
001 25
002 38
003 41

So: one row for each ID, the one with the maximum value. How can I achieve that?

I tried to sort the results with

ORDER BY VALUE DESC

and then add the statement:

SELECT TOP 1 ID, VALUE
FROM ...

but the system doesn't like it - I get an error:

FROM keyword not found where expected

I get as well an error if I try to write:

SELECT ID, MAX(VALUE)
FROM ...

Is there a way to have this max value row conditional to the ID? If not, do you know any way to do in Excel?

Thanks in advance

Solution

  • You need a simple group by aggregation query:

    SELECT ID, MAX(VALUE) AS VALUE
FROM yourTable
GROUP BY ID
ORDER BY ID;