CODEWITHSUNDEEP
mysqlsorting
dognose
dognose

Reputation: 20889

Order by date ASC, but DESC within group

I have two tables. Imagine the first one to be a directory, containing lot of files (second table).

The second table (files) is containing a modification_date.

Now, I want to select ALL Directories and sort them by their modification date ASC (so, the latest modification topmost). Instead of displaying that folder, I just want to display the OLDEST Mofified file (so, modification DESC, Group by folder_id)

Sorting ALL Files by their modification date is no problem.

My Query looks (simplified) like this:

SELECT 
  f.*,
  d.* 
FROM 
  files f
LEFT JOIN
  directories d
ON 
  f.directory_id = d.id
ORDER BY
  f.modification_date DESC

This gives me ALL files in their modification order (newest topmost) - Now, I want to Group files within a folder, to only see the OLDEST modification (they have "seen" Attributes, but taking that into account is no big deal, so once a modification has been seen, the second oldest will be displayed, etc...)

How can I sort a result by modification_date DESC, but also sort it by 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 like to have:

f4 (cause directory 2 contains the NEWEST modification (2013), but f4 is the oldest out of that folder)
f1 (cause directory 1 contains the SECOND newest modification, but f1 is the oldest out of that folder)

Any suggestions?

Upvotes: 0

Views: 492

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

One way is to get a list of max dates per directory, then join your query to that list:

SELECT
  d.name,
  f.name,
  f.modifiction_date
FROM (
  SELECT
    d_id,
    MAX(modification_date) AS MaxMod
  FROM files
  GROUP BY d_id
) LatestFiles
INNER JOIN Files f ON
  LatestFiles.d_id = f.d_id AND
  LatestFiles.MaxMod = f.modification_date
INNER JOIN directories d ON f.d_id = d.id

I don't have MySQL access today so I'm afraid this is untested, but that's the general idea.

Upvotes: 0

Thibault
Thibault

Reputation: 1596

An easy way would be to use subqueries.

Example :

SELECT 
d.*,
(
 SELECT f.id
 FROM files f
 WHERE f.directory_id=d.id
 ORDER BY f.modification_date DESC
 LIMIT 1
)
FROM directories d
ORDER BY d.modification_date ASC

But this give you only the id of the file, and you would have to make another request to get the informations of each file.

Another way might be to use HAVING statements (See http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html ).

And by searching for HAVING examples, i found this : http://www.sitepoint.com/forums/showthread.php?535271-Group-by-ID-having-MAX%28date%29-problem which should help you solve your problem.

But you could also create temporary tables like this :

Good luck with your project.

Upvotes: 0

Related Questions