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.
SQL MAX function returns one row when multiple rows have the same value.
The MAX() function returns the largest value of the selected column.
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 ).
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With