Page 1 of 1
How to check witch items are identical in a table?
Posted: Thu Apr 22, 2004 8:42 am
by WaldoMonster
I was thinking about something like:
Code: Select all
SELECT item FROM table WHERE item_is_not_unique GROUP BY item
Posted: Thu Apr 22, 2004 8:59 am
by JAM
Try something similiar to:
Code: Select all
select item from table where count(item) > 1
Posted: Thu Apr 22, 2004 9:13 am
by CoderGoblin
More complex example if you need the full row...
Given tababase table 'mytable'
Code: Select all
column1 | column2
--------+--------
1 | 1
1 | 2
1 | 3
1 | 4
2 | 2
3 | 2
The following select statement gets only those rows with duplicate values for column2:
Code: Select all
select mytable.* from mytable,(select column2,count(column2) from mytable group by column2) as tableb where tableb.count>1 and dummy.column2=tableb.column2;
The inner select gets a counter for the column you need and stores its value in temporary variable tableb.
Hope this help. (works on Postgres).
Posted: Thu Apr 22, 2004 9:26 am
by JAM
I do however belive that subqueries aren't allowed in MySQL versions below 4.1. Should be mentioned.