How to determine if a set of columns have unique values for rows in a table

By: Borland Staff

Abstract: You can run a query to see if there duplicates
Problem:
How to determine if a set of columns have unique values for rows in a table

This may be necessary when trying to create a unique index and it fails
claiming that there are duplicate values.

Solution:
The information in this article applies to:
  * InterBase v4.x
  * InterBase v5.x

Use the following query to return a count for the specified columns 
having duplicate values:

select col1, col2, ..., colx,  count(*) from tablename group by col1, col2, ..., colx having count(*) > 1;

This query will return the rows that have duplicate values.   These rows can then be updated so
that the unique index can be created.

EXAMPLE
---------------
Using the example employee.gdb database shipped with InterBase, this query
can be used to determine which customers have more than 1 purchase order

select cust_no, count(*) from sales group by cust_no having count(*) > 1;



Published on: 7/24/2000 12:00:00 AM

Server Response from: ETNASC03

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.