PHP - I want the latest in each group but am getting the oldest

Asked By Olusegun A. on 09-Aug-12 12:41 PM

Hello guys,

Pls I have some info in my mysql DB. Each rows contains info of each area, but they all belong to various groups. eg as below

ID | NAME | GROUP | DATE
6 | Lara | Triangle | 08-08-12
5 | Daniel | Box | 08-08-12
4 | John | Box | 08-07-12
3 | Sam | Circle | 08-06-12
2 | Lawrence | Triangle | 08-05-12
1 | Clara | Circle | 08-04-12

From the above table, I want to get the latest info from each group so i use the below php line

[CODE]$sql = mysql_query("SELECT * FROM $table_name GROUP BY group ORDER BY id desc LIMIT $start, $");[CODE]

but its giving me the oldest of each group (as below) instead of the latest which i want.

4 | John | Box | 08-07-12
2 | Lawrence | Triangle | 08-05-12
1 | Clara | Circle | 08-04-12




and if i change the

ORDER BY id desc

in the statement to

ORDER BY id asc

i will get

1 | Clara | Circle | 08-04-12
2 | Lawrence | Triangle | 08-05-12
4 | John | Box | 08-07-12



i want the latest in each group. am expecting the below result


6 | Lara | Triangle | 08-08-12
5 | Daniel | Box | 08-08-12

3 | Sam | Circle | 08-06-12

Pls what do I do

Peter Bromberg replied to Olusegun A. on 09-Aug-12 08:34 PM
Your SQL Statement is ordering by ID. Don't you want to order by date?