Re: Selecting unique count on non-unique index

Are you trying to verify that the combined fields actually constitute a unique key? If so, you could try: SELECT a_field1, a_field2, a_field3, count(*) FROM a_table GROUP BY a_field1, a_field2, a_field3 HAVING count(*) > 1; This will give you a list of any (proposed) key that has more than one row with that value. If, instead, you are trying to find out how many unique keys exist in the table, you could: SELECT a_field1, a_field2, a_field3, count(*) row_count FROM a_table GROUP BY a_field1, a_field2, a_field3 INTO TEMP my_temp_tbl; SELECT count(*) FROM my_temp_tbl WHERE row_count > 1; > 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? Mark Collins mcollins@us.dhl.com Words that come to mean everything may finally mean nothing; yet their very emptiness may allow them to be filled with a mesmerizing glamour.