Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MAX and returning whole row with max column value

I am new to SQL and struggling with finding a working solution for my MAX situation.

I currently have a table that looks like:

ID   Date         Version
001  2010-11-17   2
001  2010-12-01   3
002  2011-01-11   1
002  2011-05-05   2

My desired result is simply:

ID   Date        Version
001  2010-12-01  3
002  2011-05-05  2

I can't use MAX and GROUP BY on date since Date is not distinct.

like image 523
nbison Avatar asked Jun 29 '11 11:06

nbison


People also ask

Does Max returns more than one row?

SQL MAX function returns one row when multiple rows have the same value.

How do I get the max value in a column in SQL?

The MAX() function returns the largest value of the selected column.

Can we use Max in subquery?

To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).

How do I get two maximum values in SQL?

The MySQL Solution If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST: GREATEST(value1,value2,...) Given two or more arguments, it returns the largest (maximum-valued) argument.


2 Answers

You could use row_number for that, like:

select  *
from    (
        select  row_number() OVER(partition by id order by version desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1
like image 182
Andomar Avatar answered Sep 21 '22 23:09

Andomar


This should do the job:

SELECT     ID,
           Date,
           Version
FROM       YourTable
INNER JOIN 
(
    SELECT    ID,
              Max(Version)
    FROM      YourTable
    GROUP BY  ID
) AS x
ON         YourTable.ID = x.ID
AND        YourTable.Version = x.Version
like image 30
Maximilian Mayerl Avatar answered Sep 22 '22 23:09

Maximilian Mayerl