[thelist] Weird SELECT DISTINCT error
Luther, Ron
Ron.Luther at hp.com
Fri Dec 16 08:01:01 CST 2005
Matt Warden asked:
>>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?
Hi Matt,
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.
[Yes - some dbs, (like Oracle), WILL allow you to use 'distinct' on
a single column ... but the format is more like:
"Select Count(Distinct id), Count(id), Count(*)"
... which should bring back "3 4 4" for your example above.
Without the parens I'm pretty sure the distinct takes all of the
selected columns into account.]
If you wanna make things worse, throw a date/time field into your
select list ... then scratch your head for a while wondering why
the results were the same with and without the 'distinct' in the
query! Guaranteed to amuse your analyst to no end! ;-P
HTH,
RonL.
More information about the thelist
mailing list