MySQL: Group By & MAX() Question

[Imex]

Howdy again.. its been awhile.. so here goes.

I want to do a Querry like this.

SELECT * FROM 'uploads' GROUP BY 'Name' HAVING Date = MAX(Date) 

Here is the Upload DB.

ID Name   Doc        Date
1  Fruki  index.php  2002-07-01
2  Imex   core.php   2002-07-01
3  Imex   admin.php  2002-07-01
4  Imex   admin.php  2002-07-01
5  Sani   code_3.php 2002-07-02
6  Fruki  index.php  2002-07-03
7  Sani   index.php  2002-07-04

and have the output be like this (IE: Having the Dates displayed be the NEWEST entry)

6  Fruki  index.php  2002-07-03
4  Imex   admin.php  2002-07-01
7  Sani   index.php  2002-07-04

Ok, I have tried this exact querry in many different ways and all I get is the FIRST entry with the name. When I want it to display the Last Entry that was submited. Can anyone help me?

[Paul S]

I am afraid you need a sub select, which mysql currently does not support. You should do in two parts,

SELECT name,MAX(DATE) FROM uploads GROUP BY name

and after you've got the results use run

SELECT id,doc FROM uploads WHERE name=the_name_returned AND DATE=the_date_returned

for each one.

Paul

[Imex]

k, but there is still a problem its giving me the First entry of every name instead of the last entry of every name. Its printing out the * ones instead of the ~ ones. Is there a way to do like GROUP BY name DESC? so I can get the last entry first?

*1  Fruki  index.php  2002-07-01
*2  Imex   core.php   2002-07-01
3  Imex   admin.php  2002-07-01
~4  Imex   admin.php  2002-07-01
*5  Sani   code_3.php 2002-07-02
~6  Fruki  index.php  2002-07-03
~7  Sani   index.php  2002-07-04

[Paul S]

Fruki,

This is what I get after running the "SELECT name,MAX(DATE) FROM uploads GROUP BY name" query,

mysql> select name,max(date) from uploads group by name;
+-------+------------+
| name  | max(date)  |
+-------+------------+
| Fruki | 2002-07-03 |
| Imex  | 2002-07-01 |
| Sani  | 2002-07-04 |
+-------+------------+

That is what you need, isn't it?
Paul

[Imex]

yup.. maybe I did it wrong.. I will test it again.. Thanks for your help!

[Paul S]

No problem. If you have further problems just post you code.

Paul