ansaurus

Question

SQL Question: select cities distance without duplicates

Answer 1

+5 A:

SELECT c1.City, c2.City, dbo.CalcDistance(c1.x, c1.y, c2.x, c2.y)
FROM Cities c1, Cities c2
WHERE c1.CityID > c2.CityID

David B 2010-07-26 20:06:07

Cross joins make me sad :(...FROM Cities c1 JOIN Cities c2 on c1.CityID > c2.CityID please

MisterZimbu 2010-07-26 20:07:34

Either way works really. I felt the cross join syntax most properly conveys the potential performance disaster.

David B 2010-07-26 20:09:10

Thanks, is there a solution in case there is no CityId column?

2010-07-26 20:09:33

@David this is not true, `FROM Cities c1, Cities c2` is just a form of CROSS JOIN

Imre L 2010-07-26 20:11:15

@javasuperuser: Use the CityName instead (or a combination of CityName/StateName).It's definitely preferable to just have an autoincremented CityId column for performance and maintainability reasons.

MisterZimbu 2010-07-26 20:13:11

@Imre L - what is not true? In SqlServer - both queries produce the same execution plan.

David B 2010-07-26 20:14:33

@David B - I would also suggest using the ISO Cross Join syntax instead of the comma-syntax.

Thomas 2010-07-26 21:05:49

To All, this kind of rediculous inanity in the comments and voting is the reason I stopped answering SQL questions. 1. If you would do it a different way - make your own answer. 2. If you downvote someone's answer (especially due to a typo), leave a polite comment explaining why - or if you have the rep, fix the typo. It's not like the original answerer can't revert your change. 3. Off-topic answers should not get upvotes, particularly when they just link to an article that you have to create an account on some remote site just to see.

David B 2010-07-26 23:54:39

Was commenting on you saying: "cross join syntax most properly conveys the potential performance disaster."

Imre L 2010-07-27 06:02:40

Answer 2

+1 A:

SELECT
    C1.city,
    C2.city,
    <distance calculation>
FROM
    Cities C1
INNER JOIN Cities C2 ON
    C2.city_id > C1.city_id

I left the distance calculation blank since it depends on whether you're doing "as the crow flies", using street routes, accounting for curvature of the earth, etc.

Tom H. 2010-07-26 20:06:44

Just love the drive-by down votes with no explanation.

Tom H. 2010-07-26 21:00:08

Probably duplication (someone else answered the same time you did), and you have an error in your version.

Jason 2010-07-26 22:18:15

What's the error? I made an assumption that there was some kind of id (city_id), but that's not too relevant - it could just as easily be any PK column. If there is an error, then letting me know so that I could correct it would be the polite thing to do.

Tom H. 2010-07-27 00:36:10

Answer 3

+2 A:

What you are trying to do is called a "running total".

Be careful with triangular joins like "ON c1.CityID > c2.CityID", they create an cartesian product! (I don't want to argue with anybody else's answer.)

Have a look at Jeff Moden's world best article about running totals: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

If you don't have an account you will have to create one (only an email address is needed). However, I promise it's worth!

Greets Flo

Florian Reischl 2010-07-26 20:18:58

ansaurus

tags:

views:

answers:

SQL Question: select cities distance without duplicates

related questions