CODEWITHSUNDEEP
phpmysql
Latox
Latox

Reputation: 4695

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

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

Upvotes: 0

Views: 2353

Answers (2)

John Woo
John Woo

Reputation: 263723

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

Upvotes: 0

piotrekkr
piotrekkr

Reputation: 3181

Try

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

Upvotes: 3

Related Questions