SELECT DISTINCT and cleaning up redundant records

[jimoblak]

Is there a way to select records where there is more than one record containing the same fruit?

+-----------------+
| tbl_fruit       |
+-----------------+
| Apples          |
| Pears           |
| Apples          |
| Bananas         |
| Apples          |
| Bananas         |
+-----------------+

The ideal query would produce:

+-----------------+
| tbl_fruit       |
+-----------------+
| Apples          |
| Apples          |
| Apples          |
| Bananas         |
| Bananas         |
+-----------------+

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[cLFlaVA]

what version of MySQL?

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[jimoblak]

I am working with 3.23.56 and 4.0.13 on a production and development server. I am not forced to use either one (I can update to the current 4.x or 5.x beta version on the development server as well).

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[ericbrunson]

The most obvious way involves a subquery, but you don't have that luxury with those versions.

I can't think of a way off the top of my head, but I've seen r937 come up with some clever solutions to things similar.

If you upgrade your mysql versions the answer is easy.

 

[r937]

thank you, eric, for the kind words

the following works only if there is some other column in the table such as an auto_increment id

select t1.id
     , t1.fruit
  from tbl_fruit as t1
inner
  join tbl_fruit as t2
    on t1.fruit = t2.fruit
group
    by t1.id
     , t1.fruit
having count(*) > 1          
order by t1.fruit

rudy

http://r937.com/

SQL Consulting

 

[towerbase]

This seems to work for me and doesn't require an auto_increment field.

SELECT * FROM tbl_fruit ORDER BY fruit

or have I missed something obvious here?

Andrew
Hampshire, UK

 

[r937]

yes, andrew, you missed the part about selecting only those fruits where there is more than one record for that fruit

rudy

http://r937.com/

SQL Consulting

 

[towerbase]

Quite right rudy, my apologies.

Andrew
Hampshire, UK