Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: (OT) Exclusion selection on DBI

by moritz (Cardinal)
on Jan 25, 2008 at 11:47 UTC ( #664271=note: print w/replies, xml ) Need Help??


in reply to (OT) Exclusion selection on DBI

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;

Replies are listed 'Best First'.
Re^2: (OT) Exclusion selection on DBI
by fernandes (Monk) on Jan 25, 2008 at 12:37 UTC
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://664271]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (9)
As of 2021-01-22 14:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?