Join with max date

Question

Join with max date

timscronin

SSCertifiable

Points: 6851
More actions
June 29, 2010 at 12:53 pm

#234441

I have 2 tables with a one to many relationship. I have table 1 reorders with an rxno and table 2 hrxs which has many records related to the rxno with different posting dates So what I need is something like
select r.rxno,r.patid,h.sig.h.posted from reoders r
inner join hrxs h on
r.rxno = h.rxno (but i need the record from hrxs with the max posted date)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

timscronin (6/29/2010)
I have 2 tables with a one to many relationship. I have table 1 reorders with an rxno and table 2 hrxs which has many records related to the rxno with different posting dates So what I need is something like
select r.rxno,r.patid,h.sig.h.posted from reoders r
inner join hrxs h on
r.rxno = h.rxno (but i need the record from hrxs with the max posted date)

;WITH Maxhrxs AS (

SELECT rxno, max(posted) [posted]

FROM hrxs

GROUP BY rxno

)

, Newhrxs AS

select

rxno, posted, sig

from hrxs

inner join Maxhrxs

on Maxhrxs.rxno = hrxs.rxno

and Maxhrxs.posted = hrxs.posted

)

SELECT r.rxno,r.patid,h.sig.h.posted from reorders r

INNER JOIN Newhrxs h

ON h.rxno = r.rxno

Converting oxygen into carbon dioxide, since 1955.

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

got beat to the punch but here's what I came up with..

declare @reoders table (rxno int)

declare @hrxs table (rxno int, posteddate datetime)

insert into @reoders (rxno) values (1)

insert into @reoders (rxno) values (2)

insert into @hrxs (rxno, posteddate) values (1, '4/1/2010')

insert into @hrxs (rxno, posteddate) values (1, '5/1/2010')

insert into @hrxs (rxno, posteddate) values (1, '6/1/2010')

insert into @hrxs (rxno, posteddate) values (2, '6/1/2010')

select *

from @reoders r

inner join (

select rxno, posteddate, rank() over (partition by rxno order by posteddate desc) as rowrank

from @hrxs

) h on r.rxno = h.rxno

and h.rowrank = 1

_____________________________________________________________________
- Nate

@nate_hughes

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

you can do this using corelated join

select r.rxno,r.patid,h.sig.h.posted from reoders r

inner join hrxs h on

r.rxno = h.rxno

where hrxs .postedate = (select max(b.posteddate) from hrxs b

where h.id=b.id )

here you can join with your primary key in hrxs, i took it as id

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]