[thelist] Problem with distinct

Stephen Rider evolt_org at striderweb.com
Fri Sep 7 16:26:36 CDT 2007


On Sep 7, 2007, at 4:04 PM, Bill Moseley 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."
>
>     test=> select distinct on (id) * from product_view;
>      id | widget_name | color | size
>     ----+-------------+-------+-------
>       1 | widget A    | red   | large
>       2 | widget B    | red   | large
>     (2 rows)
>
> Cool!  But wait:
>
>     test=> select distinct on (id) * from product_view order by color;
>     ERROR:  SELECT DISTINCT ON expressions must match initial ORDER  
> BY expressions
>
> Darn, that's not very helpful.  What else to do?

Hi --

Maybe add another layer?  Make a query without the sort, then a  
second query pulls the first query and sorts it.

Stephen



More information about the thelist mailing list