April 9, 2012 at 3:27 pm
Hello, I have tbl called policystatus which has 5 columns policynbr, policyid, lastupdated, First Name and LastName. I wish to have the most recent updated row of policy to be used in inner join with other tables. So if 1 policy has 3 rows, with diff updated date, I wish my query to pick up the Max one. Any help appreciated, Thanks
April 9, 2012 at 3:35 pm
Can't really tell from what you have posted.
Please read and follow the instructions in this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 9, 2012 at 3:37 pm
Hopefully this helps you figure out what you need.
with ctePolicyStatus as (
select
row_number() over (partition by policyid order by lastupdated desc) as rownum,
policynbr,
policyid,
lastupdated,
firstname,
lastname
from
dbo.PolicyStatus)
select
policynbr,
policyid,
lastupdated,
firstname,
lastname
from
ctePolicyStatus
where
rownum = 1
order by
policyid,
lastupdated;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply