Page 1 of 1

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

Posted: Thu Aug 09, 2012 11:34 am
by adsegzy
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

[text]
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
[/text]

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

Code: Select all

$sql = mysql_query("SELECT * FROM $table_name GROUP BY group ORDER BY id desc LIMIT $start, $");
but its giving me the oldest of each group (as below) instead of the latest which i want.

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

and if i change the [text]ORDER BY id desc[/text]in the statement to [text]ORDER BY id asc[/text] i will get

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

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

[text]
6 | Lara | Triangle | 08-08-12
5 | Daniel | Box | 08-08-12
3 | Sam | Circle | 08-06-12
[/text]

Re: I want the latest in each group but am getting the oldes

Posted: Thu Aug 09, 2012 1:22 pm
by tr0gd0rr
I think you are looking for MAX. Maybe:

Code: Select all

SELECT `group`, MAX(`date`) 
FROM `table` 
GROUP BY `group` 
ORDER BY MAX(`date`) DESC 
LIMIT 3
I don't know if you can also get `id` and `name` for each result in that one query. Especially since it is possible that there are two records with the same date in the same group. What behavior would you want in that case?