Re: Use distinct on one column but want 2 columns of data returned

Eric,

So, if your table may has rows like below, what do you want as a result?
Col1, Col2
1, 15
1, 23
If you want only one instance of Col1 = 1, then you have to choose what Col2
will be, MIN, MAX, SUM, AVG, and so forth. By recasting as a group by
instead of a distinct you could do this:

Select TableA.Col1, Min(TableA.Col2) AS MinCol2,
Max(TableA.Col2) AS MaxCol2, Count(*) AS DuplicateCol1Count
From TableA
Where Not Exists
(
Select *
From TableB
Where TableA.Col1 = TableB.Col3
)
Group by TableA.Col1

RLF


"elittle" <elittle@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7B627CB0-8AF6-4A03-802E-06F21DDECE93@xxxxxxxxxxxxxxxx
Thank you in advance for any and all assistance. I am trying to do a
comparison of 2 tables and I am expecting the result to be what ever is
not
in one of the tables. The table I need the result from has 2 columns of
data
that I want returned based on col1 being distinct because I know it has
duplicates in it.

Ex. TableA: Col1, Col2 I want my result to have both columns of data
based
on Col1 being distinct

TableB: Col3

I compare TableA.Col1 to TableB.Col3 whatever is NOT in TableB but is in
TableA should be in the result.

I have tried this query:

Select distinct TableA.Col1,TableA.Col2
From TableA
Left Join TableB ON Table.Col1 = TableB.Col3
Where TableB is Null

The result I get has duplicates.

I have also tried this query:

Select distinct TableA.Col1,TableA.Col2
From TableA
Where Not Exists
(
Select *
From TableB
Where TableA.Col1 = TableB.Col3
)

Again the result contains duplicate data. Can anyone clarify forme what I
am doing wrong?

Thanks,
Erik


.

Relevant Pages