Select latest record if column has 2 of the same

I have table

**id name status date**

1 john 2 01.01.2010
2 mike 5 04.01.2010
3 john 2 06.01.2010
4 sam  1 08.01.2010

john has status 2 twice and i need to select john,mike from this table where status = 2 but i need to show latest record. I cannot use orde开发者_JAVA百科r by i use it already for something else.

You can use order by for multiple criteria like this:

ORDER BY date desc, status desc

You need to use a correlated subquery such as this:

select * 
from table t1
where t1.date = ( select max( t2.date )
                  from table t2
                  where t1.name = t2.name
                  and t1.status = t2.status )

The query would go much faster if you didn't need the ID field:

SELECT t.name, t.status, max(t.date) date
FROM table t
GROUP BY t.name, t.status
ORDER BY [whatever]

If you DID need id, AND the ID is guarenteed to be larger on the record with the newer date, you could just add max(t.id) id to the field list.

SELECT *
FROM table t
WHERE status = 2
AND date = (SELECT MAX(date) FROM table tmp WHERE tmp.name = t.name GROUP BY name)