Filter out certain rows based on condition

Question

I have this Input Table:

+-------------+--------------+-------+
| ProjectName | ProjectOwner | Col2  |
+-------------+--------------+-------+
| A           | Sara         | Sara  |
| B           | Jack         | Maria |
| B           | Jack         | Jack  |
| C           | Laura        | May   |
| C           | Laura        | Laura |
| D           | Paul         | Jack  |
| E           | May          | May   |
| E           | May          | May   |
+-------------+--------------+-------+

I want to check each project separately then exclude

If the project owner has his name inside col2 and other people with him, then remove the row which is repetitive Jack Jack,

a. For example, In Project B Jack has his name inside Col2 and he is working with Maria , so remove Jack Jack

b. Also remove Laura Laura in Project C
If Project owner only himself working inside the project then keep it, like Project A and Project E
If Project owner doesn't have his name inside col2 then exclude the project like Project D
Remove duplicate like Project E

Output Table:

+-------------+--------------+-------+
| ProjectName | ProjectOwner | Col2  |
+-------------+--------------+-------+
| A           | Sara         | Sara  |
| B           | Jack         | Maria |
| C           | Laura        | May   |
| E           | May          | May   | 
+-------------+--------------+-------+

I tried this here but the results didn't show me Project A and E.

Please clarify for 4: is there another table field for which you want to keep the min/max/something other entry? — George Menoutis, Jun 25 '18 at 13:26

Hamed Naeemaei · Accepted Answer · 2018-06-25T13:43:46.743

1

Please try this code:

select distinct  *
from mytable m
where (ProjectOwner!=Col2 or not exists(select 1 from mytable m1 where m1.ProjectName=m.ProjectName and  m1.ProjectOwner=m.ProjectOwner and m.Col2!=m1.Col2) )
and ProjectName in (select ProjectName from mytable where ProjectOwner=Col2 )

Demo

edited Jun 25 '18 at 13:43

answered Jun 25 '18 at 13:21

Hamed Naeemaei

8,052
3
37
46

1

!= won't work on sql server, the different-than operator is <> – George Menoutis Jun 25 '18 at 13:24
3

@GeorgeMenoutis != is a valid operator on SQL Server. – DB101 Jun 25 '18 at 13:30
2

@GeorgeMenoutis != and <> work in sql server please view this link [link](https://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-t-sql?answertab=active#tab-top) – Hamed Naeemaei Jun 25 '18 at 13:35

Yogesh Sharma · Answer 2 · 2018-06-25T13:39:18.003

Use EXISTS WITH ROW_NUMBER() function :

select top (1) with ties *
from table t
where exists (select 1 
              from table t1 
              where t1.ProjectName = t.ProjectName and 
                    t1.Col2 = t.ProjectOwner
             )
order by dense_rank() over (partition by ProjectName 
                            order by (case when t.ProjectOwner = Col2 
                                           then 1 else 0 
                                       end)
                           );

score 0 · Answer 3 · answered Jun 25 '18 at 13:31

You may get the desired results by this nested SQL by using GROUPING and COUNT agggregation :

select ProjectName, ProjectOwner, Col2
  from
(  
   select ProjectName, ProjectOwner, Col2,
          count(1) over ( partition by ProjectName ) cnt
     from mytable
    group by ProjectName, ProjectOwner, Col2
) q
 where ( q.cnt = 1 or ProjectOwner != col2 )
   and ProjectOwner in ( select col2 from mytable );

SQL Fiddle Demo

Filter out certain rows based on condition

3 Answers3