SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Rank function to filter the records ..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Naveensrcl
Starting Member

India
8 Posts

Posted - 08/12/2013 :  00:02:02  Show Profile  Reply with Quote
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

Lvl	Level1_pk_id	Level2_pk_id	Level3_pk_id
2	11	23	NULL
3	11	22	33
3	11	22	34
1	12	NULL	NULL

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

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/12/2013 :  10:56:57  Show Profile  Reply with Quote


; with CTE AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Level1_pk_id order by Level1_pk_id, Level2_pk_id, Level3_pk_id) AS RN, * from #Temp)
SELECT T1.lvl, T1.Level1_pk_id, T1.Level2_pk_id, T1.Level3_pk_id FROM 
CTE T1 LEFT JOIN CTE T2 ON T1.Level1_pk_id = T2.Level1_pk_id and T1.RN = T2.RN-1
LEFT JOIN CTE T3 ON T1.Level1_pk_id = T3.Level1_pk_id and T1.RN = T3.RN-2 
WHERE T3.Level3_pk_id IS NULL;

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000