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.