SELECT name, color, count(name) as c FROM fruit GROUP BY name HAVING c
+ >= 2 ORDER BY fruit;
[download]

I don't know if that's standard sql, but it should work with MySQL

Update: I just tested something similar with mysql 5, and the alias doesn't seem to be necessary, so just SELECT name, color FROM fruit GROUP BY name HAVING COUNT(name) >= 2 ORDER BY fruit;

I'm going to update the node. There is a column I've not talked about.
Anyway, the problem about GROUP approach is that it selects only the last row of each group. It produces:

Orange, green
Mellon, white

But I really need to see the complete list of rows where fruit.name is repeated:

Orange, yellow
Orange, green
Mellon, red
Mellon, white

Thanks!

Actually moritz's solution gives you the data you want:

[59] 192.168.174.128 5010.my_db2.(sa).1> select * from fruit;
 t                    c
 -------------------- --------------------
 orange               yellow
 orange               red
 banana               yellow
 mellon               yellow
 mellon               red
 raisin               green

(6 rows affected)

[61] 192.168.174.128 5010.my_db2.(sa).1> select t, c, count(*) from fr
+uit group by t having count(*) > 1 order by t;
 t                    c
 -------------------- -------------------- -----------
 mellon               red                            2
 mellon               yellow                         2
 orange               red                            2
 orange               yellow                         2

(4 rows affected)
[download]

This is using Sybase ASE 15.

Michael

Speusipo:

You really ought to mark your updates better so people reading the thread aren't confused. In this case, you could have left the original message alone and just added the new information.

Anyway, on to your question: Just add ',color' to the 'group by' clause in moritz's answer.

...roboticus

select fruit, color
  from fruit_table
 where fruit in (select fruit 
                   from fruit_table
                  group by fruit
                  having count(*) > 1)
 order by fruit
[download]

Michael

Update: moritz's solution is cleaner (and probably faster, if it matters), but has an additional column in the result set (which probably doesn't matter, so a better solution overall :-)

mpeppler:

Your solution does however offer the chance to bring the thread back on topic. He could write a perl script to strip off the unwanted column! ;^)

Oh, by the way, if you should ever travel to Louisville, KY, let me know. I owe you a couple cases of beer or some such for all your work on the Sybase modules, etc. They've helped me quite a bit when I started with perl and Sybase.

...roboticus

I'll accept a virtual case :-) - the probability of my going anywhere near Louisville is rather small at the moment (I'm in Switzerland...)

Michael

Thank you very much for the advices. They have given to me deep insights about my problem. But I need to apologize I’ve not described the problem appropriately. I will try it again, here:

The complete table I have is like:

Fruit, colour, Id number of supermarket

I need to be able to select only the repeated fruits in two given supermarkets. So, if I have

Mellon, red, 1
Mellon, green, 2
Banana, black, 1
Orange, blue, 3

And if I want to get information about supermarket 1 and 2, the result will be

Mellon, red, 1
Mellon, green, 2

I’m using DBI module.

Thanks!

The additional requirement of the supermarket id just adds a WHERE clause WHERE supermarket in (1, 2).

And of course the supermarket ID has to appear in your SELECT list.

It seems to me you're trying to select the data, and not count it. Maybe a complete drop/create/insert/select cycle of this helps you:

-- drop table if exists supermarket; -- will completely erase the whol
+e table, uncomment only if safe :)

-- create a new table:
create table supermarket (fruit text, color text, supermarket_id integ
+er);

-- insert data:
insert into supermarket (fruit, color, supermarket_id)
                 values ('Orange', 'yellow', 1);
insert into supermarket (fruit, color, supermarket_id)
                 values ('Orange', 'green' , 2);
insert into supermarket (fruit, color, supermarket_id)
                 values ('Orange', 'blue'  , 3);
insert into supermarket (fruit, color, supermarket_id)
                 values ('Grape' , 'black' , 1);
insert into supermarket (fruit, color, supermarket_id)
                values ('Strawberry', 'blue'  , 1);
insert into supermarket (fruit, color, supermarket_id)
                values ('Mellon' , 'red'   , 2);
insert into supermarket (fruit, color, supermarket_id)
                values ('Mellon' , 'green' , 2);
insert into supermarket (fruit, color, supermarket_id)
                values ('Mellon' , 'white' , 1);
insert into supermarket (fruit, color, supermarket_id)
                values ('Banana', 'black' , 1);

-- select data:
select 
    fruit,
    color,
    supermarket_id
from
    supermarket
where 
    supermarket_id in (1,2)
  and     fruit = 'Orange'
order by
    fruit

/* -- resultset:
   "Orange", "yellow", 1
   "Orange", "green" , 2
*/
[download]

(SQL is code and can (should?) be formatted)