[thelist] Problem with distinct

[Bill Moseley]

On Fri, Sep 07, 2007 at 05:49:27PM -0400, Matt Warden wrote:
> On 9/7/07, Bill Moseley <moseley at hank.org> wrote:
> > Client says:  "I want to be able to sort the widget list by its color".
> >
> > Developer: "But you can't do that because a widget might have more
> > than one color.  Which color would would you sort by?"
> >
> > Client: "90% of our widgets only have one color, so sort by that color.  For
> > the very few that have more than one color I don't care which one is used
> > for sorting."
> 
> 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.

> try this:
> 
> 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)



Hum, but:

 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;

 id | widget_name | color | size  
----+-------------+-------+-------
  1 | widget A    | blue  | large
  2 | widget B    | blue  | large
  1 | widget A    | red   | large
  2 | widget B    | red   | large
(4 rows)

-- 
Bill Moseley
moseley at hank.org