[thelist] Problem with distinct

[Matt Warden]

On 9/7/07, Bill Moseley <moseley at hank.org> wrote:
> > Distinct is evil
>
> Yes, I get caught be it often.  Not sure where the evilness is in my
> example is, though.  Adding an extra layer as Stephen suggested does
> seem to be a solution.

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.

> > select widget_name, max(color)
> > from t_widgets
> > where color='red'
> > group by widget_name
> > order by widget_name
> >
> > Not entirely sure it will allow you to do that on a varchar...
>
> Seems to work:
>
>  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 color = 'blue'
>    GROUP BY id, widget_name;
>
>  id | widget_name | color | size
> ----+-------------+-------+-------
>   1 | widget A    | blue  | small
>   2 | widget B    | blue  | small
> (2 rows)

You definitely do not want to do this, as there is no guarantee that
the the color and size that come up are a valid color-size
combination. Maybe blue is only available in medium and large, for
example. I was under the impression you only cared about the size.
Could you explain exactly the data you need out?

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


This email proudly and graciously contributes to entropy.