[thelist] Weird SELECT DISTINCT error

[Matt Warden]

Ron,


On 12/16/05, Luther, Ron <Ron.Luther at hp.com> wrote:
> Matt Warden noted:
>
> >>... it is difficult to see how DISTINCT could operate on only one
> column.
>
>
> Hi Matt,
>
>
> ?Que?
...
> Soooooo ... running a simple 'select distinct' on a single column turns
> out to be a nice neat trick to get a quick list of all of the unique
> values in that column.

Yes, of course. The stuff that you replaced with a '...' gives the
appropriate context to what I was saying:

"Actually, you are selecting unique combinations of opp_id and
policy_name. If you think about it, since results are returned in rows,
it is difficult to see how DISTINCT could operate on only one column."

The meaning here is that when someone is selecting MORE than one
column and includes the DISTINCT keyword, it is difficult to see how
the keyword would operate on only one of those columns. I was giving
this as justification for my claim that DISTINCT will instead give you
distinct *combinations* of those columns.

Brain thought he had run a test query to prove otherwise, to which I
gave an example to understand exactly what he saw in his test query
(who knows, maybe MSSQL Server does soemthing weird). To my
hypothetical, you picked up on exactly what I was trying to show:

">>So, for something like this:

>>id    name
>>1     bob
>>1     matt
>>2     jane
>>3     mary

>>select distinct id, name from table;

>>what does it do for id 1?

I'm pretty sure it will bring back:

1   bob
1   matt


The command isn't 'Select (distinct id), name from table'

It's more of a 'Select distinct (id, name) from table'.

I believe the 'distinct' is operating on the _all_ of the fields
you are bringing back in your recordset, so you are bringing back
all unique combinations."

Exactly. That is what I meant by saying that DISTINCT operates on a
row basis, since that's how results are returned.

In fact, in MySQL, DISTINCTROW is a synonym for DISTINCT.

Thanks,

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.