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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

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

Post 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]
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

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

Post 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?
Post Reply