Filter rows over partitions

Suppose I have the following table:

    +-------+------------+
    | nGroup |   Status   |
    +-------+------------+
    |     1 | DONE       |
    |     1 | UNKNOWN    |
    |     1 | DONE       |
    |     2 | INPROGRESS |
    |     2 | INPROGRESS |
    |     2 | DONE       |
    |     3 | INPROGRESS |
    |     3 | DONE       |
    |     3 | DONE       |
    +-------+------------+

If a nGroup has one Status to INPROGRESS, then return only the lines of that group containing the status INPROGRESS.

If all the Status of a nGroup are different from INPROGRESS, then return all the status available for that group.

So for this example, I would get the following output:

+-------+------------+
| nGroup |   Status   |
+-------+------------+
|     1 | DONE       |
|     1 | UNKNOWN    |
|     1 | DONE       |
|     2 | INPROGRESS |
|     2 | INPROGRESS |
|     3 | INPROGRESS |
+-------+------------+

I tried the following query :

SELECT *
FROM dbo.myTable T1 
WHERE Status IN (
  CASE WHEN EXISTS( SELECT 1 FROM myTable T2 WHERE T2.STATUS = 'INPROGRESS' AND T1.nGroup = T2.nGroup) THEN 'INPROGRESS'
  ELSE (SELECT Status FROM myTable T2 WHERE T1.nGroup = T2.nGroup) 
  END
)

But I get the following error when running it :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer

This will do it:

select m.*
from myTable m
where 
  m.Status = 'INPROGRESS' 
  OR NOT EXISTS (
    select 1 from myTable where nGroup = m.nGroup and Status = 'INPROGRESS'
  )

Advertisement

Answer