CODEWITHSUNDEEP
sqloracle-databaseselect
MAYBEMEDIC
MAYBEMEDIC

Reputation: 69

Oracle 10g Select records that satisfy a certain condition

I'd appreciate some help with following query.. I have a table as below,

UID | Status | Msg_Id | Msg_Id_type
1   | START  | AA     | GLOBAL
2   | END    | AA     | GLOBAL
3   | PROC   | AA     | GLOBAL
4   | START  | BB     | GLOBAL
5   | START  | CC     | GLOBAL
6   | START  | DD     | GLOBAL
7   | END    | DD     | GLOBAL

What I'd like to achieve is SELECT only those records, that do not have any other STATUS than "START" for a given MSG_ID or all records that do not have any other STATUS except "START". Thanks much for the help.

Upvotes: 1

Views: 388

Answers (3)

gustavodidomenico
gustavodidomenico

Reputation: 4681

This should help you:

SELECT msg_id  
  FROM TABLE  
 GROUP by msg_id  
HAVING COUNT(CASE WHEN STATUS = 'START' THEN 1 ELSE NULL END) = 1  
   AND COUNT(CASE WHEN STATUS <> 'START' THEN 1 ELSE NULL END) = 0

`

Upvotes: 0

Roger
Roger

Reputation: 2952

How about this:

SELECT *
FROM my_table a
WHERE NOT EXISTS (
        SELECT 1
        FROM my_table b
        WHERE b.status <> 'START'
            AND b.msg_id = a.msg_id
    )
    AND a.status = 'START' -- < I think you can ignore this line.

Upvotes: 0

user330315
user330315

Reputation:

This will give you all msg_ids that have only START as their status.

select msg_id
from the_table
where msg_id in (select msg_id from the_table where status = 'START')
group by msg_id
having count(distinct status) = 1

Upvotes: 1

Related Questions