July 25, 2013 at 12:27 pm
Hello All, could you please assist me how to get data based on min rank
DDL
Create table TableA1 (PatID int, blockName varchar(20), RankID int)
insert into TableA1 values (123,'Cancer', 5)
insert into TableA1 values (235,'Hearts', 6)
insert into TableA1 values (345,'Child',1)
insert into TableA1 values (123,'OutPatient',3)
insert into TableA1 values (567,'OutPatient',4)
insert into TableA1 values (789,'Inbound' ,7)
insert into TableA1 values (567,'OutPatient',3)
insert into TableA1 values (678,'Cancer',5)
insert into TableA1 values (789,'Hearts',6)
insert into TableA1 values (789,'KidneySpl',9)
insert into TableA1 values (345,'OutPatient',3)
select * from TableA1 order by 1
Create table TableB1 (PatID int, ModelId int )
insert into TableB1 values (123,114346)
insert into TableB1 values (235,226554)
insert into TableB1 values (345,336544)
insert into TableB1 values (567,446789)
insert into TableB1 values (678,558987)
insert into TableB1 values (789,667998)
select * from TableB1 order by 1
--Joining
select a.*, b.ModelID from TableA1 A inner join TableB1 B
on a.PatID = b.PatID
order by 1,3
Required outcome is based on the RankID . So if for the same PatID that appears more times in TableA1, the ModelID (from TableB) goes to the record with highest rankID.
expected Results will be
PatIDblockNameRankIDModelID
123OutPatient3114346
123Cancer 5
235Hearts 6226554
345Child 1336544
345OutPatient3
567OutPatient3446789
567OutPatient4
678Cancer 5558987
789Hearts 6667998
789Inbound 7
789KidneySpl9
Thank you in advance
dhani
July 25, 2013 at 5:02 pm
Here's a simple solution you could use
select a.*,
CASE WHEN a.RankID = MIN(a.RankID) OVER( PARTITION BY a.PatID)
THEN b.ModelID /*ELSE 0*/ END ModelID
from TableA1 A
inner join TableB1 B on a.PatID = b.PatID
order by 1,3
The commented code will put 0's instead of NULLS if uncommented.
July 25, 2013 at 8:50 pm
Thank you very much Luis ,
it is really great solution , and simpler form
i greatful to you,
Thank you
Dhani
July 26, 2013 at 8:11 am
I'm glad I could help.
Thank you for the feedback.;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply