ordering rows by data from another table

If I have two tables,

First table1 :

id   name

1    John
2    Andrew
3    Serj
4    Mike
5    Adam

and Second table2 :

 user_id  count  date

  开发者_如何学运维2       0      01-09-2011...
  5       9      05-09-2011...
  1       5      05-09-2011...
  3       7      04-09-2011...

How can I Select users from table1 and order them by count, date values from table2 ( DESC )

The result I want :

1 -- Adam ( count = 9 , date = 05.. )
2 -- John ( count = 5 , date = 05.. )
3 -- Serj ( count = 7 , date = 04.. )
4 ...
5 ...
...

If is not possible, or is it hard to get result I want ( see my result), then just order by count and date.

---

select * from table1 t1 
join table2 t2 on t1.id = t2.user_id 
order by date desc, count desc

---

Use JOIN.

SELECT name from table1
JOIN table2 ON table1.id = table2.user_id
ORDER BY table2.count, table2.date DESC

---

try it also please

select * from table1 t1 
join table2 t2 on t1.id = t2.user_id 
order by date, count desc

---

Using INNER JOIN

   SELECT t1.name, t2.count, t2.date
   FROM table1 AS t1 INNER JOIN table2 AS t2 
   ON t1.id = t2.user_id
   ORDER BY t2.count DESC, t2.date DESC;