Oracle query to select rows with unique code

Question

I have a table like this

C1 C2 C3 Code
1   2  3   33
1   2  3   34
2   4  1   14
1   2  3   14

i want to select only those record whose code is appearing only in single row. ie, in this case rows with code 33 and 34.. as they appear only once in this table.

How can i write a query for that

Answer 1

SELECT C1, C2, C3, Code FROM tablename
WHERE Code IN
(
   SELECT Code FROM tablename
   GROUP BY Code 
   HAVING count(Code) = 1
)

Answer 2

select C1, C2, C3, Code 
from tablename T1
where not exists ( select T2.exclude
                     from tablename T2
                    where T2.Code = T1.Code
                      and T2.rowid <> T1.rowid
                 )

PS. Watch out for NULL values in the Code column

Answer 3

If you want only one pass over your data, then you can use this query:

SQL> create table mytable (c1,c2,c3,code)
  2  as
  3  select 1, 2, 3, 33 from dual union all
  4  select 1, 2, 3, 34 from dual union all
  5  select 2, 4, 1, 14 from dual union all
  6  select 1, 2, 3, 14 from dual
  7  /

Table created.

SQL> set autotrace on
SQL> select max(c1) c1
  2       , max(c2) c2
  3       , max(c3) c3
  4       , code
  5    from mytable
  6   group by code
  7  having count(*) = 1
  8  /

        C1         C2         C3       CODE
---------- ---------- ---------- ----------
         1          2          3         33
         1          2          3         34

2 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     SORT (GROUP BY)
   3    2       TABLE ACCESS (FULL) OF 'MYTABLE'

Regards, Rob.