mySQL Query, group by and then order by most recent grouped?

Question

Say I have the following table:

CREATE TABLE `table` (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    userid INT UNSIGNED NOT NULL,
    reference INT,
    `datetime` DATETIME
) Engine=InnoDB;

I want to select from the table, group by the reference and order by DATE, but also order by the latest reference entry?

For example:

reference: 79
datetime: 2011-12-31 00:32:30

reference: 77
datetime: 2011-12-31 00:40:30

reference: 77
datetime: 2011-12-31 00:43:30

reference: 77
datetime: 2011-12-31 00:45:30

reference: 78
datetime: 2011-12-31 00:47:30

They should show in this order: 78, 77 (the 00:45 one), 79

I currently have this as my query:

SELECT * 
  FROM `table` 
  WHERE `userid` = '" . mysql_real_escape_string($id) . "' 
  GROUP BY `reference` 
  ORDER BY `datetime` DESC

How can I get this query to work? So when a reference which already exists gets another entry, it jumps to the top of the list?

Thank you

The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. — outis, Jan 01 '12 at 12:45
possible duplicate of [Select most recent row with GROUP BY in MySQL](http://stackoverflow.com/questions/5688063/), [Mysql query most recent entry with a twist](http://stackoverflow.com/questions/4125515/), [MySQL query, MAX() + GROUP BY](http://stackoverflow.com/questions/5657446/), ... — outis, Jan 01 '12 at 12:59

score 3 · Answer 1 · answered Jan 01 '12 at 12:41

3

Try

SELECT id, userid, reference, MAX(datetime) AS datetime
FROM `table` WHERE `userid` = ID 
GROUP BY `reference` 
ORDER BY `datetime` DESC

answered Jan 01 '12 at 12:41

piotrekkr

2,785
2
21
35

score 0 · Answer 2 · answered Jan 01 '12 at 12:46

0

you need to specify all the columns near Group By clause.

SELECT id, userid, reference, MAX(datetime) AS datetime
FROM `table` WHERE `userid` = ID 
GROUP BY `id`, `userid`, `reference`
ORDER BY `datetime` DESC

answered Jan 01 '12 at 12:46

John Woo

258,903
69
498
492

@johntotetwoo not in mySQL you don't [See here](http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html) – xQbert Jan 01 '12 at 13:00
There is no need to do this because query takes rows for only one user (`WHERE userid = ID`) – piotrekkr Jan 01 '12 at 13:01

mySQL Query, group by and then order by most recent grouped?

2 Answers2