Selecting unique count on non-unique index 
Author Message
 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



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;

Quote:
> 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

Words that come to mean everything may finally mean nothing; yet
their very emptiness may allow them to be filled with a mesmerizing
glamour.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Selecting unique count on non-unique index

2. Unique vs non-unique for covering index

3. Unique vs Non-unique Indexes

4. Unique Vs Non-unique Indexes

5. Unique vs non-unique for covering index

6. Indexes : UNIQUE vs non-unique

7. Unique or non-unique indices

8. How to detect an insert error of a non-unique value to a unique field

9. Unique field allowing non unique entry?

10. How to detect an insert error of a non-unique value to a unique field

11. Pg uses non-unique index instead of pkey index

12. Unique constraint and Unique Index


 
Powered by phpBB® Forum Software