SQL Puzzle 14: Puzzle Lite #2 on 'roids 5

[vongrunt]

Take data from SQL Puzzle Lite #2 (see thread183-1197121).

Make result set containing 48 rows and three columns: N (tinyint), State and Capital

1st row (N=2): state/capital closest to two other capitals
2nd row (N=3): state/capital closest to three other capitals
...
repeat until N=49

Criteria "closest" is based on sum of distances. For N=2 example, every city has two nearest cities - but (only) one has minimal sum of distances to nearest two cities and that is... Boston, I think.

Include sum in result set if you want.

Best exec time (measured w/ profiler) wins it all.

Plz no cheating - especially about Hawaii

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[vongrunt]

Actually primary key on (Capital, distance) should be faster...

Of course that will crash if one capital city has two same distances (very unlikely but...).

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[gmmastros]

right you are vongrunt. With that mod to chrissie's code, I get...

Vongrunt: 0.17 seconds
gmmastros: 0.30 seconds
chrisse1: 2.8 seconds

Without the mod, chrissie's code is running at 48 seconds.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

OK, chri<.ss<.ie got a sta<.r

I'll eventually post some "clean" queries (SQL2005 included) with explanations later.

Next puzzle is yours.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

I did not participate but here are stars anyway
And yes Chrissie I am giving you a star

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[chrissie1]

Yeepie.

I tried your suggestion george and yes it improved a lot down to 13 secs.

Then I changed them back to temp tables and it stayed at 13 secs

Christiaan Baes
Belgium

"My new site" - Me

 

[gmmastros]

Star to vongrunt for fastest execution.
Star to chrissie for learning the most.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chrissie1]

Does that mean I have to give stars aswell?

Christiaan Baes
Belgium

"My new site" - Me