1

I have encountered an issue while writing a stored procedure for displaying some records of a table. The following code is to illustrate the issue.

declare @t table(
    Id int primary key identity(1,1),
    ParentId int null,
    Item varchar(50)
)
-----------------------------------------------------------
insert into @t(ParentId,Item)
select null,'A' union all
select null,'A' union all
select 1,'B' union all
select 1,'B' union all
select null,'C' union all
select 2,'D' union all
select 2,'D'

The data after insertion in the table @t looks like this.

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
4      1         B
5     NULL       C
6      2         D
7      2         D

I want to select all records whose ParentId is null but select only the first record of those whose ParenId is not null.

Desired Result:

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
5     NULL       C
6      2         D

Any idea on how to get the desired result?

2

Assuming "only the first record of those" means ordered by Id you can use rank() partitioned by ParentId and a case statement in the order by clause.

select T.Id, 
       T.ParentId,
       T.Item
from (
     select T.Id, 
            T.ParentId,
            T.Item,
            rank() over(partition by T.ParentId 
                        order by case when T.ParentID is not null 
                                   then T.Id 
                                   else null
                                 end ) as rn
     from @t as T
     ) as T
where T.rn = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.