CODEWITHSUNDEEP
sqlsql-serveroracle-database
Nishant
Nishant

Reputation: 21914

SQL query to filter unique status records

I need a SQL Server and Oracle compatible query to get the following result

Table:

PRIMARY IDN   SECONDARY_IDN    STATUS 
1             47               Pending
2             47               Completed
3             47               Error
4             57               Pending
5             59               Completed
6             60               Pending
7             60               Completed

My input would be either Pending, Completed, or Error.

I need to list out all the secondary IDN with just 1 status and that is the input status.

For example my input is Pending: it should show up 57 ONLY. Others might have Pending but it also has completed and error records .

Can you please help me ?

Upvotes: 0

Views: 525

Answers (4)

wildplasser
wildplasser

Reputation: 44230

SELECT  *
FROM  tableName tn
WHERE tn.Status = 'Pending'
AND NOT EXISTS ( SELECT *
  FROM tableName nx
  WHERE nx.SECONDARY_IDN = tn.SECONDARY_IDN
  AND nx.Status <> 'Pending'
  );
  • The outer query has no group by, so all columns are available to it (the dreaded select * is there to illustrate this fact)
  • The exists needs to detect only one unwanted record to yield true, solutions with aggregates (min, max, count) may have to scan (and aggregate) the whole group to establish the desirability of the record.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

 select status
 , secondary_idn
 , count(*) records
 from theTable
 where whatever
 group by status, secondary_idn
 having count(*) = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need groups that have only one status. For that, you want to use aggregation:

select secondary_idn
from t
group by secondary_idn
having max(status) = min(status)  and -- all the statuses are the same
       max(status) = 'Pending'        -- and the status is Pending

Upvotes: 3

John Woo
John Woo

Reputation: 263693

SELECT  SECONDARY_IDN
FROM    tableName
GROUP   BY SECONDARY_IDN
HAVING  SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) = COUNT(*)

Upvotes: 5

Related Questions