Get the latest status

[JoZ via AccessMonster.com]

I have two tables:
tbl1: id, start_dt, end_dt
tbl2, id, status, status_change_dt

tbl2 can have multiple rows for each id to show an id's different status
changed at different time. I need to join the two tbls and then only get the
latest status for each id. I can do two queries in Access (step1: Join 2tbls,
group and sort by Ascending; step2: get the 1st record of qry1). Can someone
write a SQL to do the same? I need to bring it to a unix environment. thx.

 

[Allen Browne]

Use a subquery.
Something like this:

SELECT id, start_dt, end_dt,
(SELECT TOP 1 status
FROM tbl2
WHERE tbl2.id = tbl1.id
ORDER BY tbl2.stats_change_dt DESC, tbl2.id) AS TheStatus
FROM tbl1;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[coedxiao via AccessMonster.com]

Thank you very much. I adapted your idea to my environment and it worked!

Use a subquery.
Something like this:

SELECT id, start_dt, end_dt,
(SELECT TOP 1 status
FROM tbl2
WHERE tbl2.id = tbl1.id
ORDER BY tbl2.stats_change_dt DESC, tbl2.id) AS TheStatus
FROM tbl1;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

I have two tables:
tbl1: id, start_dt, end_dt

[quoted text clipped - 8 lines]

someone
write a SQL to do the same? I need to bring it to a unix environment. thx.