Re: Remove duplicate rows

On Thu, Jan 11, 2007 at 18:51:57 +0100,
Jiří Němec <konference(at)menea(dot)cz> 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
>
> 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
>
> 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?

Is that column dependent (just on) the column you are checking for duplicates
on? If so you can use GROUP BY on both columns, listing the column you want
to order by first. If not, you might want to take a look at DISTINCT ON.