Re: Remove duplicate rows

Jiří Němec wrote:
> Hello,
>
> I need to remove duplicates rows from a subquery but order these
> results by a column what is not selected. There are logically two
> solutions but no works.
>
> SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
>
I'm not sure here, so I'll leave it alone.
> SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar
> ERROR: column "sub.bar" must appear in the GROUP BY clause or be used
> in an aggregate function
>
The problem here is that you are not really asking a meaningful question...
foo bar
1 1
1 2

now, you are selecting foo, but you want to order by bar. What decision
should be made about which value of bar to pick, so you can order on it?

Regards

Russell Smith
> Does anybody know how to remove duplicate rows from a subquery and order
> these results by a column what is not selected but exists in a subquery?
>
> Thanks for any advice,
>
> J.N.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>