Need Rank function to filter the records

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  August 11, 2013 at 10:04 pm

  #395279

  Hi

  CREATE TABLE #Temp

  (

  Lvl int

  ,Level1_pk_id int

  ,Level2_pk_id int

  ,Level3_pk_id int

  )

  INSERT INTO #Temp

  SELECT 1,11,null,null UNION ALL

  SELECT 2,11,22,null UNION ALL

  SELECT 2,11,23,null UNION ALL

  SELECT 3,11,22,33 UNION ALL

  SELECT 3,11,22,34 UNION ALL

  SELECT 1,12,null,null

  Here , Need to select the last records for each level1,level2,level3 comibation.

  Out should be as below

  select * from #Temp

  LvlLevel1_pk_idLevel2_pk_idLevel3_pk_id

  21123NULL

  3112233

  3112234

  112NULLNULL

  Logic:

  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 (112233) and (112234) will be the final result.

  11 and 23 does not have level3 so 11 and 23 should be come in result

  12 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!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 12, 2013 at 5:12 am

  #1640509

  I'd guess you have used a recursive cte to generate this data. It would help if you were to post the rCTE query and perhaps some sample data to run too.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  August 12, 2013 at 5:58 am

  #1640523

  Clumsy but appears to work

  SELECT t1.Lvl,t1.Level1_pk_id,t1.Level2_pk_id,t1.Level3_pk_id

  FROM #Temp t1

  WHERE NOT EXISTS(SELECT * FROM #Temp t2

  WHERE t2.Lvl = t1.Lvl + 1

  AND ((t1.Lvl = 1 AND t2.Level1_pk_id = t1.Level1_pk_id)

  OR (t1.Lvl = 2 AND t2.Level1_pk_id = t1.Level1_pk_id AND t2.Level2_pk_id = t1.Level2_pk_id)

  OR (t1.Lvl = 3 AND t2.Level1_pk_id = t1.Level1_pk_id AND t2.Level2_pk_id = t1.Level2_pk_id AND t2.Level3_pk_id = t1.Level3_pk_id)))

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537