[thelist] Problem with distinct
Bill Moseley
moseley at hank.org
Fri Sep 7 17:07:26 CDT 2007
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
More information about the thelist
mailing list