Modifying the group by column to get the newest result on top

Question

Hey

I am sorry if this has been already posted or its on internet. I came here after long search

Suppose this is the table:

+----+-------+----------+---------------------+
| id | name  | group_id | created_time        |
+----+-------+----------+---------------------+
|  1 | foo   | 1        | 2010-09-22 00:00:00 |
|  2 | rafi  | 2        | 2010-09-23 00:00:00 |
|  3 | rafi1 | 2        | 2010-09-24 00:00:00 |
|  4 | rafi2 | 2        | 2010-09-25 00:00:00 |
|  5 | bar   | 5        | 2010-09-26 00:00:00 |
|  6 | baz   | 6        | 2010-09-27 00:00:00 |
|  7 | baz1  | 6        | 2010-09-26 00:00:00 |
|  8 | rafi3 | 2        | 2010-09-24 00:00:00 |
|  9 | baz2  | 6        | 2010-09-30 00:00:00 |
+----+-------+----------+---------------------+

What I want is to group these according to group ids and order it by created_time desc(newer first)

but when i say

SELECT id,name,group_id,created_time FROM test group by group_id ORDER BY id desc;

I get this

+----+------+----------+---------------------+
| id | name | group_id | created_time        |
+----+------+----------+---------------------+
|  6 | baz  | 6        | 2010-09-27 00:00:00 |
|  5 | bar  | 5        | 2010-09-26 00:00:00 |
|  2 | rafi | 2        | 2010-09-23 00:00:00 |
|  1 | foo  | 1        | 2010-09-22 00:00:00 |
+----+------+----------+---------------------+

what i want is to get something like this

+----+------+----------+---------------------+
| id | name | group_id | created_time        |
+----+------+----------+---------------------+
|  9 | baz2 | 6        | 2010-09-30 00:00:00 |
|  5 | bar  | 5        | 2010-09-26 00:00:00 |
|  5 | rafi2| 2        | 2010-09-25 00:00:00 |
|  1 | foo  | 1        | 2010-09-22 00:00:00 |
+----+------+----------+---------------------+

I have tried

SELECT max(date(created_time)) as foo,name,group_id FROM test group by group_id ORDER BY foo desc;

I get the dates right but cant get the name right.

Answer 1

How about this? Is using a subquery an option - if so, this might work!

SELECT id, name, group_id WHERE id IN (Select max(id) FROM test group by group_id) ORDER BY id desc;

Answer 2

does that query fit your needs?

SELECT t1.id, t1.name, t1.group_id FROM Test t1
INNER JOIN
  (SELECT MAX(id) as maxid FROM Test GROUP BY group_id) t2
ON t2.maxid = t1.id
ORDER BY t1.id DESC;

EDIT:

if you want to order by a datetime field you can slightly modify the query above:

SELECT t1.id, t1.name, t1.group_id, t1.created_date FROM Test t1
INNER JOIN
  (SELECT MAX(created_date) as maxdate, group_id FROM Test GROUP BY group_id) t2
ON (t2.maxdate = t1.created_date AND t2.group_id = t1.group_id)
ORDER BY t1.created_date DESC;

Is it still what you're looking for?