Display all parent records but only one record of child records of a parent record

by Asrar Ahmad Ehsan   Last Updated June 12, 2019 06:06 AM

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?

Tags : sql-server t-sql


Related Questions


Updated September 03, 2018 18:06 PM

Updated March 16, 2016 08:02 AM

Updated July 10, 2018 15:06 PM

Updated April 17, 2019 14:06 PM

Updated April 18, 2019 11:06 AM