mysql - Order by date ASC, but DESC within group -

i have 2 tables. imagine first 1 directory, containing lot of files (second table).

the second table (files) containing modification_date.

now, want select directories , sort them modification date asc (so, latest modification topmost). instead of displaying folder, want display oldest mofified file (so, modification desc, group folder_id)

sorting files modification date no problem.

my query looks (simplified) this:

select    f.*,   d.*     files f left join   directories d on    f.directory_id = d.id order   f.modification_date desc 

this gives me files in modification order (newest topmost) - now, want group files within folder, see oldest modification (they have "seen" attributes, taking account no big deal, once modification has been seen, second oldest displayed, etc...)

how can sort result by modification_date desc, sort modification_date asc after grouping it?

example:

directories:

id | name  1    folder 1 2    folder 2 

files

id | name | d_id | modification_datee 1    f1     1       2008-01-01 2    f2     1       2011-01-01 3    f3     2       2013-01-01 4    f4     2       2010-01-01 

result i'd have:

f4 (cause directory 2 contains newest modification (2013), f4 oldest out of folder) f1 (cause directory 1 contains second newest modification, f1 oldest out of folder) 

any suggestions?

an easy way use subqueries.

example :

 select  d.*, (  select f.id  files f  f.directory_id=d.id  order f.modification_date desc  limit 1 ) directories d order d.modification_date asc  

but give id of file, , have make request informations of each file.

another way might use having statements (see http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html ).

and searching having examples, found : http://www.sitepoint.com/forums/showthread.php?535271-group-by-id-having-max%28date%29-problem should solve problem.

but create temporary tables :

good luck project.