Thread: Remove duplicate rows
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? Thanks for any advice, J.N.
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 > > >
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.