oracle sql: How to not select select duplicate records from table

Question

Can any help me to fix the below sql query

       SELECT DISTINCT ORDER_NUMBER, FLAG
FROM TABLE

Below was the result

Order_number FLAG

LP-13288 false

LP-13288 true

LP-13292 false

LP-13290 true

LP-13293

LP-13291 true

LP-13289 false

Desired output

Order_number FLAG

LP-13290 true

LP-13291 true

how to not select the duplicate orders and only print the order which has flag true.

Any inputs are appreciated.

Thanks

Answer 1

You can use NOT IN:

    SELECT DISTINCT t1.ORDER_NUMBER, t1.FLAG
    FROM TABLE t1
    WHERE t1.FLAG='true' AND t1.ORDER_NUMBER NOT IN 
    (SELECT t2.ORDER_NUMBER FROM TABLE t2
    WHERE t2.FLAG='false' OR t2.FLAG IS NULL)

If you don't care about nulls (that is you only want to exclude order numbers that explicitly have a false flag) just remove the "OR t2.FLAG IS NULL" condition. Also this query will return orders where possibly more than one records with flag 'true' exist (as long as there are no records with flag 'false' or null)