Re: NEED SQL HELP PLEASE, SELECT DISTINCT QUESTION
zigzag wrote:
>
> I have a table which contains several columns. I would like to retrieve
> the rows from this table, but remove duplicates based on one column
> only.
>
> For example:
>
> Row# Col A Col B Col C
> 1 cat AA BB
> 2 cat BB CC
> 3 dog CC DD
> 4 hat DD EE
> 5 dog EE FF
>
> I want to select rows so that they are unique on Col A only, so the set
> returned would only
> contain rows, 1,3 and 4.
>
> If I was writing this Select in my own language it would be something
> like this:
> "Select ColA, ColB, ColC from table where ColA is unique."
>
> Of course if I only want ColA, it's easy:
> "Select distinct ColA from table;"
>
> Why am I having such a hard time with this seemingly easy chore?
> ZZ
Because as far as SQL is concerned why 1,3 & 4 and not 2,4 & 5? And
since the dependent column values in ColB and ColC are different in
each of the pairs of rows (1&2 & 3&5) there is value lost be selecting
one over the other. Anyway try this:
select colA, min(ColB), min(ColC)
from table
group by ColA;
Of course this could not return what you want if min(ColB) and
min(ColC) come from different rows. The two step is needed:
select min(rowid) rid, ColA
from table
group by ColA
into temp fred;
select ColA, ColB, ColC
from table, fred
where table.rowid = fred.rid;
Of course that's not guaranteed to return 1,3 & 4 and is just as likely
to return 1, 4 & 5 or 2,3 & 4 etc. If you want the one with the
minumum ColB value, then make the first select read:
select ColA, min(rowid) rid, min(ColB) ColB
from table
group by ColA
into temp fred;
This can be folded into a subquery but will tend to run MUCH slower.
Art S. Kagel