Retrieve rows of a table with only a particular column value-CodePudding

I have a table as below. I want to pick up those rows which has only 'needs to run' for particular columnA and columnB.In the below example the result should be only the 3rd row.

columnA columnB columnC
123     abc     needs to run
123     abc     suceeded
111     abc     needs to run

CodePudding user response：

We can try to use the window function with condition aggregate in subquery, then compare the count which only 'needs to run'

SELECT t1.columnA,
       t1.columnB,
       t1.columnC
FROM (
    SELECT t1.*,
           COUNT(*) OVER(PARTITION BY columnA,columnB) cnt1,
           COUNT(CASE WHEN columnC = 'needs to run' THEN 1 END) OVER(PARTITION BY columnA,columnB) cnt2
    FROM T t1 
) t1
WHERE t1.cnt1 = t1.cnt2

CodePudding user response：

You can use:

SELECT columnA,
       columnB,
       columnC
FROM   (
  SELECT columnA,
         columnB,
         columnC,
         SUM(CASE WHEN columnC = 'needs to run' THEN 0 ELSE 1 END)
           OVER (PARTITION BY columnA, columnB) AS has_other
  FROM   table_name
)
WHERE  has_other = 0;

CodePudding user response：

SELECT table1.*
FROM table1
INNER JOIN (
    SELECT columnA, columnB
    FROM table1
    GROUP BY columnA, columnB
    HAVING count(*) = 1
    ) sub ON table1.columnA = sub.columnA
    AND table1.columnB = sub.columnB
    AND table1.columnC = 'needs to run'