Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive ctes
30. Traveling salesman 3
Summary

Instruction

Great! Finally, it's time to construct the whole query!

Exercise

Now, carefully read carefully the instructions below and modify the template in the proper way.

We want to visit every city, starting from London. List all paths which start in London and visit all six of the cities in the City table. Show the following columns:

  • Path – the list of consecutive cities, separated by N'->', e.g. London->Oxford->Cambridge...),
  • LastLat and LastLon – the coordinates of the last city in the path,
  • TotalDistance – how much we need to travel altogether,
  • CountPlaces – a helper column that should show the number of cities in the path.

Order the results by the TotalDistance in ascending order.

You can use the following function that we prepared for you to calculate the distance between two cities:

dbo.LatLonDistance(lat1,lon1,lat2,lon2)

Stuck? Here's a hint!

The London CTE should become your anchor member. The second part of the outer query should become the recursive member, but you need to construct the Path column by referring to the path from the previous recursive step. In the outer query, show only those paths where the number of cities equals 6 and order the rows in ascending order by TotalDistance.

The last problem is how to construct the termination check. We can use the function CHARINDEX(a, b), which will return 0 if string a is not contained in string b. In other words, we only want to add a certain city if it didn't appear in the path so far.

We have added appropriate casts in the anchor member to help you with the query.