CODEWITHSUNDEEP
sqlpostgresqldistinct-on
shivam thakur
shivam thakur

Reputation: 119

Why doesn't my DISTINCT ON expression work?

Query:

SELECT DISTINCT ON (geom_line),gid 
FROM edge_table;

I have a edge table which contains duplicates and I want to remove duplicate edges keeping one of them, but the syntax itself is wrong?

Upvotes: 0

Views: 63

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247043

The comma is the problem.

If you want geom_line included in the result, use

SELECT DISTINCT ON (geom_line) geom_line, gid FROM edge_table;

Else use

SELECT DISTINCT ON (geom_line) gid FROM edge_table;

But if your objective is just to remove duplicates, I'd say that you should use

SELECT DISTINCT geom_line, gid FROM edge_table;

DISTINCT guarantees uniqueness over the whole result set, while DISTINCT ON guarantees uniqueness over the expression in parentheses. If there are several rows where the expression in parentheses is identical, one of these rows is picked. If you have an ORDER BY clause, the first row will be picked.

DISTINCT a, b is the same as DISTINCT ON (a, b) a, b.

Upvotes: 1

Related Questions