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.