Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 20th, 2003, 04:25 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 20th, 2003, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
  #3 (permalink)  
Old June 20th, 2003, 05:09 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
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.
Reply With Quote
  #4 (permalink)  
Old June 20th, 2003, 06:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:33 PM.


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