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

# Query suggestions needed

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