Re: [SQLObject] DISTINCT and COUNT

[Ian B. <ia...@co...>]

David M. Cook wrote:
> On Mon, Sep 13, 2004 at 06:01:09PM -0500, Ian Bicking wrote:
> 
> 
>>I'm still a little confused about what distinct really means.  No two 
>>rows can be indistinct if you are including the primary key in the 
>>select.  So what does distinct accomplish?  If you could select a subset 
> 
> 
> I think you're thinking of distinct in the sense of distinguishable.  SQL
> distinct returns a set of rows without duplications like the unix uniq
> command.  This is most useful when combined with count(distinct) (or the
> sqlite workaround of using a subselect) when you need a count of distinct rows
> in a set without the upfront cost of iterating through the whole set and
> removing duplicates.  For example,
> 
>   res = Composer.select(Work.q.composerID==Composer.q.id)

OK, that makes more sense to me.  It feels a little odd still; it would 
be most natural to do this with a query like:

SELECT composer.all columns... FROM composer WHERE EXISTS (SELECT * FROM 
work WHERE work.composer_id = composer.id);

Not all databases can do that, of course (particularly MySQL), but maybe 
it would be better to make the SELECT DISTINCT a workaround, and expose 
something like EXISTS.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org