August 11, 2013 at 10:04 pm
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!
August 12, 2013 at 5:12 am
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.
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
August 12, 2013 at 5:58 am
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/61537Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply