Home Why doesn't my DISTINCT ON expression work?
Reply: 1

Why doesn't my DISTINCT ON expression work?

shivam thakur
1#
shivam thakur Published in 2017-12-07 07:23:12Z

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?

Laurenz Albe
2#
Laurenz Albe Reply to 2017-12-07 17:12:34Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.36741 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO