Selecting unique values from an Oracle table

Comments (1) | Share

There are times when we only want to the unique records from a table where there are many records that have the same value.  In this example, I have a table that stores all the coating weight footage measurements for a strip of steel as it moves thru the zinc pot. 

The primary key of this table is composed of the four columns:

so, as one can see, from the primary key definition, a single coil, as identified by WTAG, which is the coil number, can have multiple records as identified by the recorded footage, FT_CWG

For example, the number of coating weight records for coil 606210 stored in our table is 44 records:

   1:  select count(*) from galv_coat_wt_ga where wtag = '606210';

For the date of July 2nd, 2010, there are 10693 records but only 251 distinct produced coils. 

To get the number of stored coating weight records (10693 records), I issue this SQL statement:

   1:  select count(*) from galv_coat_wt_ga where insert_dts between to_date('02-jul-2010 00:00:00', 'DD-MON-YYYY HH24:MI:SS') and

   2:  to_date('03-jul-2010 23:59:59', 'DD-MON-YYYY HH24:MI:SS');

To get the number of distinctly produced coils (251 coils) :

   1:  select count(*) from

   2:    (select distinct wtag from galv_coat_wt_ga where insert_dts between to_date('02-jul-2010 00:00:00', 'DD-MON-YYYY HH24:MI:SS') and

   3:    to_date('03-jul-2010 23:59:59', 'DD-MON-YYYY HH24:MI:SS'));

To view the list of distinct coils:

   1:  select distinct wtag from galv_coat_wt_ga where insert_dts between to_date('02-jul-2010 00:00:00', 'DD-MON-YYYY HH24:MI:SS') and

   2:  to_date('03-jul-2010 23:59:59', 'DD-MON-YYYY HH24:MI:SS');