[thelist] Problem with distinct

Matt Warden mwarden at gmail.com
Fri Sep 7 17:18:10 CDT 2007

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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list