Ordering rows based on cross reference between columns I have a table like this:


Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
3     3     4     4
4     4     3     3
5     5     4     4


Is it possible to return the rows in the following order?


Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
5     5     4     4
4     4     3     3
3     3     4     4


There is a unique rowid column.

The (Lat1,Lng1) and (Lat2,Lng2) pairs will always be unique.

lat1 and long1 of row n+1 must be the same as lat2 and long2 of row n.
You can do this with a hierarchical recursive CTE.

The only problem is that you have a loop in your data, due to there being 3,3,4,4 and 4,4, 3,3.

drop table #tmp;

create table #tmp
(
lat1 int,
lng1 int,
lat2 int,
lng2 int
);

insert into #tmp values (1,1,2,2);
insert into #tmp values (2,2,5,5);
insert into #tmp values (3,3,4,4);
insert into #tmp values (4,4,3,3);
insert into #tmp values (5,5,4,4);

WITH cte AS
(
  SELECT 
   lat1,lng1,lat2,lng2,
    0 AS level, cast('('+cast(lat1 as varchar) + ',' + cast(lng1 as varchar)+')' as varchar) as path 
  FROM #tmp
  WHERE lat1=1 and lng1=1
  UNION ALL
  SELECT 
  t.lat1,t.lng1,t.lat2,t.lng2, 
  level+1, cast(path+','+'('+cast(t.lat2 as varchar) + ',' + cast(t.lng2 as varchar)+')' as varchar)
  FROM #tmp t
  INNER JOIN cte c
    ON c.lat2 = t.lat1 and c.lng2 = t.lng1
)
select * from cte; 
;


The WHERE lat1=1 and lng1=1 part specifies the base of the hierarchy (which is probably the start of a journey, in this case).