plutov.by plutov.by - 2 months ago 7
MySQL Question

Mysql group by one field and order by another

Maybe this question is duplicate, but I can not find an answer for my task. I have the next table with following data:

full table data

I need to select data with unique

oid
and with max date for each oid. I have these queries, but they work incorrectly:

SELECT `oid`, `pvalue`, `date`
FROM `report`
WHERE `deviceid` = 'MRA-1011'
AND `date` <= '2012-01-20 00:00:00'
GROUP BY oid
HAVING `date` = MAX(`date`)
ORDER BY `date` DESC;

SELECT `oid`, `pvalue`
FROM `report`
WHERE `deviceid` = 'MRA-1011'
AND `date` <= '2012-01-20 00:00:00'
GROUP BY oid
ORDER BY `date` DESC;


I want to get this result:

correct result

Answer

You need to use a subselect along the lines of:

SELECT oid, pvalue FROM report
JOIN (SELECT oid, MAX(date) AS maxDate FROM report r WHERE `deviceid` = 'MRA-1011'
AND `date` <= '2012-01-20 00:00:00' GROUP BY oid) AS foo
ON foo.oid=r.oid AND foo.maxDate = report.date;

if report.date isn't unique you might want to group by it in the outer query