How to find values of column status that have 8 but still do not have 9

Question

Iam working on project and i need to find in table one column named status (this col have values from 1-12), i want to find all values that below to the condition (if the status have 8 but still do not have 9), for more information :

Original table :

    File_name           id      status  Execution_number
    Live_package1.zip   333      3      4444443434343
    Live_package2.zip   444      7      8888888886767
    Live_package1.zip   333      8      4444443434343
    Live_package2.zip   444      8      8888888886767
    Live_package2.zip   444     10      8888888886767
    Live_package3.zip   666      8      9999999999999
    Live_package4.zip   111      9      3333333333333

The desired result

File_name            id       status    Execution_number
Live_package1.zip   333         8        4444443434343
Live_package3.zip   666         8        9999999999999

iam using the following code , but I did not received the result what I expected:

SELECT *
FROM tbl_doc_state
WHERE status       =8
AND NOT(status  !=9);

Answer 1

Something like this?

SQL> select * From tbl_doc_state;

FIL         ID     STATUS EXECUTION_NUMBER
--- ---------- ---------- ----------------
lp1        333          3               43
lp2        444          7               67
lp1        333          8               43
lp2        444         10               67
lp3        666          8               99
lp4        111          9               33

6 rows selected.

SQL> with temp as
  2    (select id,
  3            max(status) max_status
  4     from tbl_doc_state
  5     group by id
  6    )
  7  select a.*
  8  from tbl_doc_state a join temp t on a.id = t.id
  9                                  and a.status = t.max_status
 10  where t.max_status = 8;

FIL         ID     STATUS EXECUTION_NUMBER
--- ---------- ---------- ----------------
lp1        333          8               43
lp3        666          8               99

SQL>