Home Why doesn't my DISTINCT ON expression work?

# 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.
Processed in 0.36741 second(s) , Gzip On .