Post reply

How to write a codition based on min rank

  • July 25, 2013 at 12:27 pm

    #280958

    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

    #1635980

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 25, 2013 at 8:50 pm

    #1636016

    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

    #1636172

    I'm glad I could help.

    Thank you for the feedback.;-)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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