Very tricky challenge

12th_Man · June 20th, 2003, 04:25 PM

Hello all-

Sorry for posting twice in a row.

Here's my situation (or at least a simplified illustration of it).

I have a table of cities and their distances along a highway. So if we assume the Pacific coast to be 0 and there was a theoretical interstate that connected all these cities the table might look like this:

LA 20
Pheonix 350
Santa Fe 500
Dallas 1000
Atlanta 2500

I need to get a list of the cities in order with the corresponding distance from the previos city. So from the above table I would get:

LA 20
Pheonix 330 /* 350 - 20 = 330 */
Santa Fe 150 /*500 - 150 = 150 */
Dallas 500
Atlanta 1500

I've been racking my brain trying to figure out how to do this. Please let me know if you have any ideas.

Thanks,
Mike

Jeff Mason · June 20th, 2003, 04:46 PM

Assuming a table definition:

Code:

CREATE TABLE Distances
   (City varchar(20) not null Primary Key,
    Distance integer not null);

Then the query would be:

Code:

SELECT City, Distance -
   COALESCE((SELECT MAX(distance) FROM Distances D2
               WHERE D2.Distance < D1.Distance), 0) as CityDifference
FROM Distances D1
ORDER BY Distance;

The idea is that the correlated subquery finds the maximum distance for all the cities whose distance value is less than the current row. This would be the city just 'before' the current one. Subtracting this maximum value from the current distance value gives the distance from the prior city.

The COALESCE is in there to protect the subtraction for the first city (LA). Because there are no cities 'less than' LA, the subquery would return null, and thus LA's distance minus a null gives null. I assume you really want LA's distance as the first entry and the COALESCE provides that.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

12th_Man · June 20th, 2003, 05:09 PM

Thanks Jeff.

Another guy gave me this solution:

SELECT City, Difference
FROM
((SELECT testTable.City, testTable.Distance, MIN(testTable.Distance - table2.Distance) AS Difference
FROM testTable, testTable table2
WHERE testTable.City != table2.City AND testTable.Distance - table2.Distance >= 0
GROUP BY testTable.City, testTable.Distance)

UNION

(SELECT testTable.City, testTable.Distance, testTable.Distance AS Difference
FROM testTable
WHERE testTable.Distance = (SELECT MIN(Distance) FROM testTable))) AS table1

Yours looks to be much more elegant.

Jeff Mason · June 20th, 2003, 06:45 PM

Quote:

quote:Originally posted by 12th_Man
Yours looks to be much more elegant.

Only if it works... ;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com