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!
Re: (OT) Exclusion selection on DBI
by moritz (Cardinal) on Jan 25, 2008 at 11:47 UTC
|
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; | [reply] [d/l] [select] |
|
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!
| [reply] |
|
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
| [reply] [d/l] |
|
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
| [reply] |
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 :-)
| [reply] [d/l] |
|
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
| [reply] |
|
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
| [reply] |
|
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!
| [reply] |
|
| [reply] [d/l] |
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) | [reply] [d/l] |
|
|