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;
Last Modified: 23-OCT-00