How to check witch items are identical in a table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

How to check witch items are identical in a table?

Post by WaldoMonster »

I was thinking about something like:

Code: Select all

SELECT item FROM table WHERE item_is_not_unique GROUP BY item
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Try something similiar to:

Code: Select all

select item from table where count(item) > 1
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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).
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

I do however belive that subqueries aren't allowed in MySQL versions below 4.1. Should be mentioned.
Post Reply