CREATE TABLE statuser
([userid] int)
;
INSERT INTO statuser
([userid])
VALUES
(1),
(2)
;
CREATE TABLE stat
([userid] int, [status] int, [startdate] date)
;
INSERT INTO stat
([userid], [status], [startdate])
VALUES
(1, 1, '2012-05-30'),
(1, 2, '2013-06-23')
;
with maxstat as (
select s.userid,max(startdate) startdate
from statuser su
left outer join stat s
on su.userid = s.userid
group by s.userid
)
select s.userid,s.status,s.startdate
from maxstat m
join stat s
on m.userid = s.userid
and m.startdate = s.startdate
Oh, also make a habit of having the same name of the fields in all tables, so if it's called USER_ID in one table don't call it PEOPLE_FK in another. It's much easier to debug that way.
Wrong is evil and must be defeated. - Jeff Ello[^]
