Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
markpirvine
Starting Member
3 Posts |
 Posted - 2010-01-15 : 06:20:56
|
| Hi,I have two tables that I cross join to produce a result set with over 20,000 records. These results are placed into a new table - Table C. Table A contains job details and location (latitude/lonitude coordinates), Table B contains engineer details and location (latitude/lonitude coordinates). As part of the cross join a distance calculation is made between each job and engineer. What I would like to do is delete, from Table C, the engineers that are NOT in the top 5 (ordered by distance (ascending)) for each job.How could I go about this?Mark |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-15 : 07:14:39
|
So you have already a select that gives all the records including the calculated distance - right?Then please try this (not sure, could not test):insert table_c (col1, col2, ...)select top 5 with ties col1, col2, ...from (here comes your select statement...) as dtorder by distance DESC
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
Cross Join Limit