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

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

HTML:

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

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

PHP:

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

HTML:

and if i change the

ORDER BY id desc

PHP:

in the statement to

ORDER BY id asc

PHP:

i will get

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

HTML:

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

HTML:

Pls what do I do

 

Hey!

You could do something like this:

SELECT `o`.* from `$table_name` `o`,
(select `group`, MAX(`id`) as `maxId` FROM `$table_name` GROUP BY `group`) `t`
WHERE `o`.`group` = `t`.`group` and `o`.`id` = `t`.`maxId`
ORDER BY `o`.`id` DESC LIMIT $limit

Code (markup):

Probably not the most efficient, but it works

 

Please use this query to get your results
SELECT * , max( id )
FROM `users`
GROUP BY group_name
ORDER BY id ASC

 

SELECT * , max( id )
FROM `users`
GROUP BY group_name
ORDER BY id ASC

 

Just realized that your IDs are most likely unique, so you can shorten the WHERE-statement a bit, since the group=group is redundant:

SELECT `o`.* from `$table_name` `o`,
(select `group`, MAX(`id`) as `maxId` FROM `$table_name` GROUP BY `group`) `t`
WHERE `o`.`id` = `t`.`maxId`
ORDER BY `o`.`id` DESC LIMIT $limit

Code (markup):