5 Replies Latest reply: Jan 6, 2005 3:36 PM by Gabe2 RSS

    Query suggestions needed

    435902
      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
          Kevin Tyson
          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
            Brian Camire
            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
              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
                Brian Camire
                Enclose the text between a [PRE] tag and a [/PRE] tag.
                • 5. Re: Query suggestions needed
                  Gabe2
                    select 'Cheers 200458'
                    from   dual;
                  I was toying with <pre> and </pre> ... hmmm ... thanks again.