CODEWITHSUNDEEP
sqlpostgresqljoin
D_C
D_C

Reputation: 390

Duplicate alternate columns

I have a table created by joining a table to itself. 

SELECT a.id id1, b.id id2, ST_Distance(a.geom, b.geom) dist
FROM yy_cluster_1 a, yy_cluster_1 b 
WHERE ST_DWithin(a.geom, b.geom, 12) AND a.id <> b.id
ORDER BY a.id, b.id

This results in a table like the one below on the left. I am trying to number rows to produce the result on the right

enter image description here

For the life of me I cannot think how to do this, let alone efficiently.

Upvotes: 1

Views: 33

Answers (1)

GMB
GMB

Reputation: 222402

Use dense_rank() along with least() and greatest():

SELECT 
    a.id id1, 
    b.id id2, 
    ST_Distance(a.geom, b.geom) dist,
    dense_rank() over(order by least(a.id, b.id), greatest(a.id, b.id)) new_id
FROM 
    yy_cluster_1 a
    INNER JOIN yy_cluster_1 b ON ST_DWithin(a.geom, b.geom, 12) AND a.id <> b.id
ORDER BY a.id, b.id

Note that I changed your query so it performs an explicit join instead of an old-school implicit join.

Upvotes: 2

Related Questions