Re: Selecting unique count on non-unique index

On Mon, 09 Feb 1998 10:28:47 GMT, ian@tubeway-army.demon.co.uk (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? > Hi All, Many thanks to those of you who contributed an answer to my query, both here and via Email. The favourite solution given was: select a_field1, a_field2, a_field3, count(*) from a_table group by 1,2,3 having count(*) > 1 The solution to this query and that of another I placed in this newsgroup last week utilise the GROUP BY and HAVING in the answer. It seems to me a thorough read of the use of these two statements is in order :-) Cheers, Ian. Tedious 'office speak' part 3: "Let's go scuba in the think tank."