Re: Get the latest status
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Fri, 30 Jan 2009 15:13:33 +0900
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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"JoZ via AccessMonster.com" <u11274@uwe> wrote in message
news:90f0c7508f141@xxxxxx
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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200901/1
.
- References:
- Get the latest status
- From: JoZ via AccessMonster.com
- Get the latest status
- Prev by Date: Re: Finding overlapping period
- Next by Date: run multiple queries in VBA
- Previous by thread: Get the latest status
- Next by thread: run multiple queries in VBA
- Index(es):
Relevant Pages
|
