CODEWITHSUNDEEP
sqlpostgresqlpostgis
Voyteck
Voyteck

Reputation: 81

SQL list only unique / distinct values

I have a table which contains geometry lines (ways).There are lines that have a unique geometry (not repeating) and lines which have the same geometry (2,3,4 and more). I want to list only unique ones. If there are, for example, 2 lines with the same geometry I want to drop them. I tried DISTINCT but it also shows the first result from duplicated lines. I only want to see the unique ones. I tried window function but I get similar result (I get a counter on first line from the duplicating ones). Sorry for a newbie question but I'm learning :) Thanks!

Example:

way|
1  |
1  |
2  |
3  |
3  |
4  |

Result should be:

way|
2  |
4  |

That actually worked. Thanks a lot. I also have other tags in this table for every way (name, ref and few other tags) When I add them to the query I loose the segregation. 

select count(way), way, name 
from planet_osm_line 
group by way, name 
having count(way) = 1;

Without "name" in the query I get all unique values listed but I want to keep "name" for every line. With this example I stilll get all the lines in the table listed.

Upvotes: 2

Views: 5414

Answers (4)

Nithila Shanmugananthan
Nithila Shanmugananthan

Reputation: 336

@voyteck As I understood your question you need to get only non duplicating records of way column and for each row you need to show the name is it

If so, you have to put all the column in select statement, but no need to group by all the columns. select count(way), way, name from planet_osm_line group by way having count(way) = 1;

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

You first calculate the rows you want, and then search for the rest of the fields. So the aggregation doesnt cause you problems.

WITH singleRow as ( 
   select count(way), way
   from planet_osm_line 
   group by way
   having count(way) = 1
)
SELECT P.*
FROM planet_osm_line P
JOIN singleRow S
  ON P.way = S.way

Upvotes: 2

Nithila Shanmugananthan
Nithila Shanmugananthan

Reputation: 336

you can group by way and while taking the data out check the count=1.It will give non duplicating data.

Upvotes: 2

neal
neal

Reputation: 905

To expound on @Nithila answer:

select count(way), way
from your_table
group by way
having count(way) = 1;

Upvotes: 6

Related Questions