Re: How to do this query?

On 18 Apr 1995 5603liul_at_vms.csd.mu.edu wrote:

> Dear netters:
>
> I wonder how I can do this query?
>
> Assume I have a table, say A. In A, there are two columes,
> Say, B, C. Let's assmue it looks like this.
> B C
> --- ---
> 1 2
> 2 3
> 3 3
> 4 6
>
> How can we find the columns with duplicate C's? Here,
> it is (2,3) and (3,3).
>
> David

Here's my answer, although I'm sure there are more elegant ones:

TEST-SQL> ;
  1* select outer.b, outer.c from a outer

     where 1 < (select count('x') from a inner where inner.c = outer.c)

TEST-SQL> /

         B          C                                                                                                               
---------- ----------                                                           
         2          3                                                           
         3          3                                                                                                               

 ELAPSED: 0 00:00:00.20 CPU: 0:00:00.01 BUFIO: 6 DIRIO: 6 FAULTS: 1 Dave

David B Erickson, Project Leader     * 
McHugh Freeman                       * "YIP YIP YIP YIP YIP YIP YIP YIP YIP"
erickson_at_mfa.com                     *                           -Dino