Join statement...how do do ORDER BY?

I have the following LEFT JOIN statement (though probably also applies to even a simpler SELECT statement):

SELECT * FROM t1 LEFT JOIN t2 ON t2.c = t1.c WHERE t1.m LIKE 'captain%' GROUP BY  开发者_如何学编程   
t1.c ORDER BY t2.date DESC LIMIT 0,10

The results get returned but they are not ordered by t2.date DESC...I imagine this is due to having the GROUP BY statement in there. How can I group the results AND order them?

thx

---

Put your query in a subquery and then use ORDER BY.

SELECT *
FROM
 (SELECT * 
  FROM t1 LEFT JOIN t2 ON t2.c = t1.c 
  WHERE t1.m LIKE 'captain%' 
  GROUP BY t1.c DESC LIMIT 0,10) l1
ORDER BY date 

---

In normal SQL it would not even be allowed to ORDER BY t2.date because you aren't grouping on it and you are not using an aggregate function on it, so you can't select it.

Anyway, as MySQL just picks values from the rows in an undefined manner and afterwards sorts them, the t2.date column in your results should be sorted. Please show some actual output.