SQL Server – Display All Parent Records with One Child Record

sql servert-sql

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?

Best Answer

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