[thelist] Problem with distinct

[Matt Warden]

On 9/7/07, Bill Moseley <moseley at hank.org> wrote:
> > There is no reason to use distinct in your example; that is where the
> > evilness comes from. Also, using inner views or actual views is a
> > solution, but again I don't see any reason to do it that way.
>
> But, with the view I have I need some way to get "distinct" widgets.

Yes, but just accomplish this without using the "distinct" keyword.
The most common way to do this is using group by.

> In simple terms, I want to do a join on all the tables (so I can say
> WHERE color = 'red') but then throw out any duplicate widget ids --
> and I don't care which are thrown out.  Then on this final table be
> able to use LIMIT and ORDER BY.

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);

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

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.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.