This discussion is archived
5 Replies Latest reply: Jan 6, 2005 1:36 PM by Gabe2 RSS

Query suggestions needed

435902 Newbie
Currently Being Moderated
Please help with this complex query, I have been working on a solution for hours now. Here is a simplified version:

I have 3 fields in tableA:field1, field2, field3

I want to return all those records that have both field2 and field3 the same but field1 different.

For example:

Field1 Field2 Field3
======================
Cars Blue 6 liter
Cars Blue 6 liter
Van Blue 6 liter
Cars Green 5 liter
Cars Green 5 liter

I need the first 3 records returned because field2 and field3 are the same but field1 is different.

Anyone have any ideas?
  • 1. Re: Query suggestions needed
    410584 Newbie
    Currently Being Moderated
    SELECT field1, field2, field3 FROM tablea
    WHERE ( field2, field3 ) IN
    ( SELECT field2, field3 FROM
    (SELECT count(*), field2, field3 FROM
    ( SELECT DISTINCT field1, field2, field3 FROM tablea )
    GROUP BY field2, field3
    HAVING count(*) > 1
    )
    )

    Kevin Tyson, OCP
    Daimler Chrysler HQ in Auburn Hills, MI
  • 2. Re: Query suggestions needed
    BrianCamire Explorer
    Currently Being Moderated
    I think the second SELECT is unnessary. In other words, the following should be equivalent:
    SELECT 
        TABLEA.*
    FROM
        TABLEA
    WHERE
        (FIELD2, FIELD3)
        IN
        (
            SELECT
                FIELD2,
                FIELD3
            FROM
                (
                    SELECT DISTINCT
                        FIELD1,
                        FIELD2,
                        FIELD3
                    FROM
                        TABLEA
                )
            GROUP BY
                FIELD2,
                FIELD3
            HAVING
                COUNT(*) > 1
        )
    Assuming FIELD1 is NOT NULL, it is also possible to eliminate the SELECT DISTINCT by using COUNT(DISTINCT...), as in something like:
    SELECT 
        TABLEA.*
    FROM
        TABLEA
    WHERE
        (FIELD2, FIELD3)
        IN
        (
            SELECT
                FIELD2,
                FIELD3
            FROM
                TABLEA
            GROUP BY
                FIELD2,
                FIELD3
            HAVING
                COUNT(DISTINCT FIELD1) > 1
        )
    No doubt there are yet other solutions using analytics.
  • 3. Re: Query suggestions needed
    Gabe2 Newbie
    Currently Being Moderated
    select * from (
    select f1,f2,f3,
    count(distinct f1) over (partition by f2,f3) n
    from xxx
    ) t
    where t.n > 1;

    How does one retain the nice indentation in these postings?
  • 4. Re: Query suggestions needed
    BrianCamire Explorer
    Currently Being Moderated
    Enclose the text between a [PRE] tag and a [/PRE] tag.
  • 5. Re: Query suggestions needed
    Gabe2 Newbie
    Currently Being Moderated
      select 'Cheers 200458'
      from   dual;
    I was toying with <pre> and </pre> ... hmmm ... thanks again.