Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--