Re: Selecting unique count on non-unique index
You need to remove the where count(*) > 1 on the select from my_temp_tbl to
count how many rows would be returned with the proposed unique key
Mark Collins <mcollins@us.dhl.com> on 02/09/98 07:21:46 AM
To: Ian Clark <ian@tubeway-army.demon.co.uk>, informix-list@iiug.org
cc: (bcc: KENDRICKS CHERYL/HEB45)
Subject: 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.