Re: How to do this query?

Off the top of my head, I would think something like this would work:

select a1.B, a1.C from A a1, A a2

        where (a1.rowid != a2.rowid) and (a1.C = a2.C)

If column B is a unique key then

select a1.B, a1.C from A a1, A a2

        where (a1.B != a2.B) and (a1.C = a2.C)

although it would probably not be as fast if column B is not indexed.

You should also be able to find all but the first/last pair in the second case (e.g., if you want to delete duplicates) by replacing the != with > or < respectively.

In article <0098F132.BC764E1C_at_vms.csd.mu.edu>,  <5603liul_at_vms.csd.mu.edu> wrote:
> 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).
Received on Wed Apr 19 1995 - 00:00:00 CEST