Select distinct question

  • Thread starter Thread starter hedrew3
  • Start date Start date
H

hedrew3

I have a table with columns ID, A, B, and C.

I need to select those rows which have duplicate values of A and B,
but different values of C. And I also need the ID value.

I tried something like "select ID, distinct A, distinct B, C from
mytable", but of course, this crapped out.

Any help would be appreciated.

TIA

Pete
 
You might do this in three steps:

1. Create a query (say, named "Query1") that finds the distinct
combinations of A, B, and C, as in something like:

SELECT DISTINCT
[A],
,
[C]
FROM
[Your Table]

2. Create a query (say, named "Query2") that uses Query1 to find the
combinations of A and B for which there is more than one distinct value of
C, as in something like:

SELECT
[A],

FROM
[Query1]
GROUP BY
[A],

HAVING
Count(*) > 1

3. Create a query that finds records in your table that correspond to those
in Query2, as in something like:

SELECT
[Your Table].*
FROM
[Your Table]
INNER JOIN
[Query2]
ON
[Your Table].[A] = [Query2].[A]
AND
[Your Table]. = [Query2].

This assumes A and B are not Null.
 
Thanks for your reply. I came up with a solution somewhat like yours,
as follows:

1). Created a query (using the Duplicates wizard) that gave me all
records having a count of >1 with respect to columns A and B (also
included column ID in the query result).

2). Created a query, using the Duplicates wizard, that gave me all
records having a count >1 with respect to column C (also including
column ID). HOWEVER, the key step here is that I manually edited the
query to replace the >1 condition to say =1. This gave me a result of
those records with unique values of C.

3). Created a join of the above two queries, using ID as the join
field.

Pete
 
Our solutions will give different answers. For example, given the records

ID,A,B,C
1,10,20,300
2,10,20,400
3,30,40,400

my solution will include the record with ID = 2, but yours will not.

In your statement "...select those rows which have duplicate values of A and
B, but different values of C..", I interpreted "different values of C" as
"different values of C for the same combination of A and B".

In any case, choose whichever solution gives you the answer you want.
 
Back
Top