[thelist] Problem with distinct

[Bill Moseley]

On Fri, Sep 07, 2007 at 09:38:56PM -0400, Matt Warden wrote:
> Ok, I think you pretty much had it, then. You had:
> 
> SELECT p.id, p.widget_name, max(c.color) AS color, max(s.size) AS size
>   FROM product p
>   JOIN color c ON c.product = p.id
>   JOIN size s ON s.product = p.id
>   WHERE size = 'large'
>   GROUP BY id, widget_name, color
>   ORDER BY color;
> 
> But you do not want to group by color. That is why we are using the
> aggregate function max()
> 
> SELECT p.id, p.widget_name, max(c.color) AS color, max(s.size) AS size
>   FROM product p
>   JOIN color c ON c.product = p.id
>   JOIN size s ON s.product = p.id
>   WHERE size = 'large'
>   GROUP BY id, widget_name
>   ORDER BY max(color);

Thanks Matt.  I'm not sure why I had that extra "color" group by.
Frankly, grouping by multiple columns always seems a bit confusing.
For cases like this it seems like just grouping by the id would get
the desired results, but that's not how things work. ;)

> Again, though, the resulting color and size combo may not be valid.

Because the max(color) and max(size) may not be from the same row?
That's fine, as long as I can still say "where color = ?" or "where
size = ?" to limit to those colors or sizes.


> Your other option is to forget about getting distinct results and take
> care of that when you render. Order by widget_name and have your
> application code suppress the row any time widget_name is the same as
> the previous row. This way you will get the equivalent of a first()
> aggregate function which ensures that the color-size combo is valid.
> Frankly, given that most products have only a single color-size combo,
> this is how I would approach the issue.

Maybe so.  It just breaks the paging.  I can't but sure my LIMIT and
OFFSET calculations will be right since I don't know for sure how many
items will be on a given page until it's fetched.

Thanks!

-- 
Bill Moseley
moseley at hank.org