Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Rank function to filter the records ..

Author  Topic 

Naveensrcl
Starting Member

8 Posts

Posted - 2013-08-12 : 00:02:02
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

549 Posts

Posted - 2013-08-12 : 10:56:57
[CODE]

; 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;

[/CODE]
Go to Top of Page
   

- Advertisement -