Click here to Skip to main content
15,867,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,

I have data in a table like below :

FileId ParentId BatchId
1 null 1
2 null 1
3 null 1
4 null 1
5 null 1
6 1 1
7 1 1
8 1 1
9 2 1
10 2 1
11 3 1

I this first five Id do not have parent Id there after all the FileIds have:
I want output something like below:If an file id's have same parent id then only first fileId
should be fetched.
FileId ParentId BatchId
1 null 1
2 null 1
3 null 1
4 null 1
5 null 1
6 1 1
9 2 1
11 3 1
Posted

1 solution

SQL
  select distinct
    fileID, parentID, batchID
  from x where parentID is null
union
  select 
    min(fileID),  parentID, batchID
  from x
  where not parentID is null
  group by parentID, batchID


The first part of the union gives us :

fileID      parentID    batchID
----------- ----------- -----------
1           NULL        1
2           NULL        1
3           NULL        1
4           NULL        1
5           NULL        1


and the second part ...

6           1           1
9           2           1
11          3           1
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900