http://www.perlmonks.org?node_id=664269

fernandes has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

My table is like:

Orange, yellow, 1
Orange, green, 2
Grape, black, 1
Strawberry, blue, 1
Mellon, red, 2
Mellon, white, 1

And I want to select just the rows that repeat the fruits:

Orange, yellow, 1
Orange, green, 2
Mellon, red, 1
Mellon, white, 2

And ORDERed BY fruit.

I’ve tried subqueries and INTERSECT without good results.
Has anyone some advice?

The third column does not matter the ORDER. It is like a supermarket ID number. But I want to see only the rows of 1 and 2 supermarkets. This is important because uses a WHERE statement, I think.

Thanks!

Replies are listed 'Best First'.
Re: (OT) Exclusion selection on DBI
by moritz (Cardinal) on Jan 25, 2008 at 11:47 UTC
    Untested:
    SELECT name, color, count(name) as c FROM fruit GROUP BY name HAVING c + >= 2 ORDER BY fruit;

    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)
        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

Re: (OT) Exclusion selection on DBI
by mpeppler (Vicar) on Jan 25, 2008 at 11:51 UTC
    Obviously not a perl question, however:
    select fruit, color from fruit_table where fruit in (select fruit from fruit_table group by fruit having count(*) > 1) order by fruit
    should normally work (untested, though).

    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.

Re: (OT) Exclusion selection on DBI
by erix (Prior) on Jan 25, 2008 at 15:04 UTC

    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 */

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