CODEWITHSUNDEEP
sqlsql-servert-sql
Kimo
Kimo

Reputation: 359

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.

Upvotes: 0

Views: 49

Answers (2)

forpas
forpas

Reputation: 164069

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'
  )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Hmmm . . . I think you want:

select t.*
from dbo.myTable t
where t.status <> 'INPROGRESS' or
      exists (select 1
              from dbo.myTable t
              where db.myTable = 'INPROGRESS'
             );

Upvotes: 0

Related Questions