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

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