Hi CREATE TABLE #Temp( Lvl int ,Level1_pk_id int ,Level2_pk_id int ,Level3_pk_id int )INSERT INTO #TempSELECT 1,11,null,null UNION ALLSELECT 2,11,22,null UNION ALLSELECT 2,11,23,null UNION ALLSELECT 3,11,22,33 UNION ALLSELECT 3,11,22,34 UNION ALLSELECT 1,12,null,nullHere , Need to select the last records for each level1,level2,level3 comibation.Out should be as below select * from #TempLvl Level1_pk_id Level2_pk_id Level3_pk_id2 11 23 NULL3 11 22 333 11 22 341 12 NULL NULLLogic: Level1_pk_id =11 has 2 Level2_pk_ids 22 and 23. 22 has value for Level3_pk_id. so result for 11 and 22 combination is (11 22 33) and (11 22 34) will be the final result.11 and 23 does not have level3 so 11 and 23 should be come in result12 also does not have any child levels , so 12 should be in result set.1,11,null,null -- records should not come in final result set as it has child levels 2,11,22,null -- aslo should not come in final result set as it has child levels
Thanks!