SQL distinct if previously occuring-CodePudding

I want to collapse rows if a column has already appeared...

SQL Server - table structure:

CREATE TABLE Docs 
(
    Area varchar(max),
    RootFolder varchar(max),
    SubFolder varchar(max),
)

Raw data:

Area	RootFolder	SubFolder
One	12345
One	12345	6789
One	12345	AAAAA
One	CCCCC
One	DDDDDD
One	DDDDDD	FFFFFF
One	DDDDDD	GGGGG

I want:

Area	RootFolder	SubFolder
One	12345	6789
One	12345	AAAAA
One	CCCCC
One	DDDDDD	FFFFFF
One	DDDDDD	GGGGG

I've tried group bys and other window functions but I don't know how to get the output I want

CodePudding user response：

You want to show all rows that either are a subfolder or for which no subfolder exists.

select *
from docs
where subfolder is not null
or not exists
(
  select null
  from docs sub
  where sub.area = docs.area
  and sub.rootfolder = docs.rootfolder
  and sub.subfolder is not null
);