Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 20th, 2003, 04:25 PM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Very tricky challenge

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
 
Old June 20th, 2003, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 20th, 2003, 05:09 PM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 20th, 2003, 06:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
A little challenge... chacquard Access VBA 7 February 2nd, 2007 11:38 AM
Tricky logic (for me) cedwards Dreamweaver (all versions) 5 April 5th, 2006 04:47 PM
A Challenge... AForgue XSLT 3 November 5th, 2003 04:14 PM
Tricky SQL fastcorvette Access 2 October 10th, 2003 10:28 AM
Challenge.... developerz BOOK: ASP.NET Website Programming Problem-Design-Solution 2 September 29th, 2003 04:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.