Selecting unique records

[itbnorris]

I'm in the process of cleaning up a very ugly and very old table. We intend to use a field called "serial_num" as the primary key. However, the serial_num field is not unique due to bad data. I need to pull out all the records where serial_num is unique (the rest will be dealt with later).

This:

select unique(serial_num), field2, field3 from tablename;

... returns serial numbers that aren't unique because field2 - field3 were entered in different ways.

ex.

Serial_num field2 field3
------------ --------- --------
100 valve hand
100 valve, hand
100 vlv hd

... this returns, but serial_num is not unique though the record is. I want the whole record, but only if serial_num is unique. How do you accomplish this?

Thanks in advance!

 

[carp]

To find the unique serial numbers, try
SELECT ser_num FROM my_table
GROUP BY ser_num
HAVING COUNT(*) = 1;

 

[Turkbear]

Hi, If you want to find out which are unique use

select distinct Serial_num from tablename;

Any added fields affect the 'uniqueness' of the record.

So you will need to remove the duplicates - there is no easy way to use that table as it is, without geting duplicates..

However, if you look into the EXCEPTIONS clause associated with creating a Primary Key ( and then create a primary key on Serial_Num), you will find a way to get a table containing the rowids of the duplicate rows and you can then use this to delete from the original table.
Go to

http://tahiti.oracle.com

and search for EXCEPTIONS
Here is a brief section form there for v 9.2:

Handling Constraint Exceptions
When defining the state of a constraint, you can specify a table into which Oracle places the rowids of all rows violating the constraint.

exceptions_clause
Use the exceptions_clause syntax to define exception handling. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The EXCEPTIONS table or the table you specify must exist on your local database.

You can create the EXCEPTIONS table using one of these scripts:

UTLEXCPT.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.) 
UTLEXPT1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables. 
If you create your own exceptions table, then it must follow the format prescribed by one of these two scripts.

hth,

 

[carp]

Oops! Forgot the rest of the story!

To get records where the serial numbers are unique:

SELECT * FROM my_table
WHERE ser_num IN (SELECT ser_num FROM my_table
GROUP BY ser_num
HAVING COUNT(*) = 1);

is the way people normally try this. A faster method might be:

SELECT a.ser_num, a.field1, a.field2
FROM my_table a,
(SELECT ser_num FROM my_table
GROUP BY ser_num
HAVING COUNT(*) = 1) b
WHERE a.ser_num = b.ser_num;

 

[DannyO]

A script I often use to find unique records (and dupes) is the following:

select distinct Serial_num, count(*) from my_table
group by serial_num
order by 1;

This will list the field value and the number of recs with that value and it will order the output from lowest count to highest. If you ever want to find the stray duplicate record, do an 'order by' descending to get the highest count listed first. You can do this with one field or any number of fields in the select statement.

 

[DannyO]

Ooops, I meant to say 'order by 2' in the above post. You want to order by the count.

Sorry....

 

[sem]

Another way is

SELECT * FROM <TABLE> a
WHERE NOT EXISTS
(SELECT 1 FROM <TABLE> b WHERE a.serial_num=b.serial_num AND a.ROWID<>b.ROWID)

Regards, Dima