SQL: how do write the ORDER BY based on value within a group?

Question

I have a table

project issues updated
    1   1 2009-09-03
    1   2 2009-09-08
    2   1 2009-09-12
    2   2 2009-09-01

and I would like to sort so that the projects are sorted in descending order so that the project with the latest updated issue is first etc., but all issues of a project are kept together and the issues are in ascending order within the project based on issue number

the result should be:

project issues updated
   2    1 2009-09-12
   2    2 2009-09-01
   1    1 2009-09-03
   1    2 2009-09-08

Answer 1

Something like this should do the job:

SELECT mt.* 
FROM mytable mt
 JOIN (SELECT MAX(updated) AS LastUpdated, project 
               FROM mytable GROUP BY project) lu ON lu.project = mt.project
ORDER BY lu.LastUpdated DESC, mt.Issues

Oops, I just saw the MySQL tag. I don't know if this solution will work in MySQL

Answer 2

I think this would work (sorry, no mysql at hand, so I just tested on sqlite...):

select t.project, t.issues, t.updated from t
join (select project, max(updated) as dat
      from t group by project) as t1
  on (t.project = t1.project)
order by t1.dat desc, t.issues asc

Answer 3

SELECT p.project, p.issues, p.updated, max(r.updated)
FROM table p INNER JOIN table r ON r.project=p.project
GROUP BY p.project, p.issues, p.updated
ORDER BY 4 DESC, p.project, p.issues

I've tried an equivalent query in mysql & it looks like it works the way you want it to.