CODEWITHSUNDEEP
phpmysqlselect
Poemm
Poemm

Reputation: 109

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 order by i use it already for something else.

Upvotes: 0

Views: 128

Answers (4)

Doug Kress
Doug Kress

Reputation: 3537

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.

Upvotes: 1

Ivan Peevski
Ivan Peevski

Reputation: 963

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

Upvotes: 0

asc99c
asc99c

Reputation: 3905

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 )

Upvotes: 1

Matt Stein
Matt Stein

Reputation: 3053

You can use order by for multiple criteria like this:

ORDER BY date desc, status desc

Upvotes: 1

Related Questions