[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