Mysql group by one field and order by another

Question

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

please add the result of these queries along with what u want from it..\ — Sashi Kant
– Sashi Kant, Commented Jan 9, 2012 at 9:28

James C · Accepted Answer · 2012-01-09 09:27:34Z

3

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

answered Jan 9, 2012 at 9:27

James C

14.2k1 gold badge37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

leMoisela Over a year ago

There is a small error here. You cannot have r.oid outside the parenthesis where you define report r.

Sashi Kant · Accepted Answer · 2012-01-09 09:29:22Z

1

Try this ::

SELECT `oid`, `pvalue`, `date`, MAX(`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;

answered Jan 9, 2012 at 9:29

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

1 Comment

DivineCoder Over a year ago

This is not working for me as date is not dependent on the group by

bluish · Accepted Answer · 2012-07-27 08:28:41Z

0

SELECT `oid`, `pvalue`
FROM `report` AS `r1`
WHERE `deviceid` = 'MRA-1011'
  AND `date` <= '2012-01-20 00:00:00'
  AND `date` = ( SELECT MAX(`date`) 
                 FROM `report` AS `r2` 
                 WHERE `r1`.`oid` = `r2`.`oid`
               )
;

edited Jul 27, 2012 at 8:28

bluish

27.5k28 gold badges125 silver badges185 bronze badges

answered Jan 9, 2012 at 9:26

Alex Pliutau

22k28 gold badges115 silver badges144 bronze badges

Comments

Tudor Constantin · Accepted Answer · 2012-01-09 09:47:36Z

-1

In order to have that result, you have to

ORDER BY `date` ASC:

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

edited Jan 9, 2012 at 9:47

answered Jan 9, 2012 at 9:28

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

Collectives™ on Stack Overflow

Mysql group by one field and order by another

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Related