Re: [SQLObject] DISTINCT and COUNT

[David M. C. <da...@da...>]

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)

returns a set of musical composers who have musical works in the database.
It takes about 1.9 seconds on my machine to iterate through the resultset to
get the actual count of distinct composers (the additional time to keep
track of duplicates in a dict is not significant), which makes a big
difference in the responsiveness (or lack thereof) of an interactive
application.

Dave Cook