sql - Selecting joined distinct/last -

ok, have 2 tables, this:

person: | id | name | +----+------+ | 0  | carl | +----+------+ | 1  | max  | +----+------+ | 2  | lars |  status | personid | submitted  | status | +----------+------------+--------+ | 0        | *datetime* | 1      | +----------+------------+--------+ | 0        | *datetime* | 1      | +----------+------------+--------+ | 0        | *datetime* | 3      | +----------+------------+--------+ | 0        | *datetime* | 1      | +----------+------------+--------+ | 1        | *datetime* | 5      | +----------+------------+--------+ 

what want recive persons in user table among last submitted status.

if use this:

select distinct     person.name username,     status.status userstatus person left join status on status.personid = person.id 

i result this:

| username | userstatus | +----------+------------+ | carl     | 1          | +----------+------------+ | carl     | 3          | +----------+------------+ | max      | 5          | +----------+------------+ | lars     | null       | +----------+------------+ 

so, how can this? in case, lets latest status.submitted ==1 (last row status.personid personid == 0), skip other carls.

edit: forgot write i'm using sql compact.

edit2: got awesome answers, unfortunate forgot mention want users null status values. there fore added new user table

use aggregate function max in combination group statement.

select     person.name username,     s3.status userstatus person left join (   select s.person_id, s.status    status s   join (select max(submitted) last,person_id        status group person_id) s2   on s.person_id = s2.person_id   s.submitted = s2.last) s3 on person.id = s3.person_id; 

sql fiddle: http://sqlfiddle.com/#!2/9822b/19