CODEWITHSUNDEEP
sqloracle-database
Gayathri
Gayathri

Reputation: 349

How to eliminate unwanted data using not exists in SQL

Hi I have data like-

ORDER_NUMBER   REVISION_NUMBER
2-345                  1
2-345                  2
2-345                  3
5-436                  1
6-436                  1

Now I need to pick only those order_numbers which has only revision number 1 and that order_number should not have any other revision number like 2 ,3

In this case, it should display order_numbers - 5-436 and 6-436 since 2-345 has revision number (2,3) also.

How do i do this in SQL?

Upvotes: 0

Views: 57

Answers (4)

STaefi
STaefi

Reputation: 4377

First find the maximum revision for each ORDER_NUMBER, then filter REVISION_NUMBER > 1:

select * from 
(select ORDER_NUMBER, max(REVISION_NUMBER) as max_rev from [your_table_name] 
group by ORDER_NUMBER)
where max_rev = 1;

Upvotes: 0

TZHX
TZHX

Reputation: 5387

Since you say specifically you want to use NOT EXISTS...

SELECT * FROM Orders T WHERE T.REVISION_NUMBER = 1
AND NOT EXISTS ( SELECT * FROM Orders T2 
                WHERE T2.ORDER_NUMBER = T.ORDER_NUMBER 
                AND T2.REVISION_NUMBER <> 1)

Here's an SQL Fiddle that demonstrates it in action.

Upvotes: 2

kl78
kl78

Reputation: 1666

Another solution using not exists: 

Select ordernumber from table a
WHERE NOT EXISTS (
SELECT 1 
FROM table b 
WHERE a.ordernumber = b.ordernumber and revisionnumber != 1)

Upvotes: 2

vc 74
vc 74

Reputation: 38179

Try this

select max(order_number), revision_number
from table
group by revision_number
having count(*) = 1 and revision_number = 1

Upvotes: 1

Related Questions