Select recordset of most recent additions to a table per FK
-
Dear all,
I'm hoping there's a simple answer to this one;
I have a table with an auto-identity column (app_ID); this stores appointment records for customers, each customer having a cust_ID.
Each customer can have multiple appointments, i.e.;
app_ID,cust_ID ,Data...
1,1,x,y,z
2,1,x,y,z
3,2,x,y,z
4,3,x,y,z
5,3,x,y,z
6,2,x,y,z
I need to select the most recent appointment records for each customer into my resultset such that in the above example the following rows only would be returned;
app_ID,cust_ID ,Data...
2,1,x,y,z
5,3,x,y,z
6,2,x,y,z
Any suggestions on how to achieve this would be greatly appreciated.
-
Here's one option:
SELECT a.*
FROM myTable a
JOIN ( SELECT cust_id, Max(app_id) As MaxID
FROM myTable
GROUP BY cust_id ) b ON a.app_id = b.MaxID
wayne -
Thanks Wayne,
I'd come up with this since submitting the post;
select fu.* from dbo.followups fu
inner join
(select customer_id, max(followupID) As maxFollowUp
from dbo.followups
group by customer_id) as fuLatest
on (fu.customer_ID = fuLatest.Customer_ID AND fu.followupID = fuLatest.maxFollowUp)
which is basically what you wrote!, many thanks for the reassurance...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply