Re: Selecting unique count on non-unique index

Ian Clark wrote: > > Hi All, > > I've spotted a table in our database that has no unique key but is > nevertheless updated using a WHERE clause that identifies a > uniqueness. > > That is: > > UPDATE a_table SET a_field = a_value > WHERE a_field1 = a_value1 > AND a_field2 = a_value2 > AND a_field3 = a_value3> > The fields a_field1, a_field2, a_field3 have not been collated to form > a unique key ( which I intend to do! ). > Having found the total number of records on the table, how do I now > determine the number of records based on the uniqueness of the three > fields sited above? If you mean the number of unique values for the three keys that would be: select unique a_field1, a_field2, a_field2 from a_table into temp fred; select count(*) from fred; However, if this combined key is really unique that should be the same as 'select count(*) from a_table' so if you want to know whether it is indeed a unique key you could try: select a_field1, a_field2, a_field3, count(*) from a_table having count(*) > 1; Then the number of records returned plus the number of rows in a_table less the sum of the returned counts from this query is the number of unique keys in a_table. Art S. Kagel