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-12 : 11:47:18
|
| Hi,I have a problem ordering data within a cross join query. The cross join operates on a table called job and engineer. This produces a list of all job and engineers (lat/lon the job address and engineer start location is used to calculate the distance). The result set below shows a sample of this data:job_id job_location eng_id eng_location distance578 BT48 25 BT47 0.062985279578 BT48 30 BT47 0.062985279578 BT48 50 BT48 0.062985279578 BT48 51 BT47 0.062985279578 BT48 52 BT47 0.062985279578 BT48 182 BT47 0.062985279578 BT48 21 BT47 0.640291847578 BT48 27 BT82 9.804417058578 BT48 16 BT82 9.804417058578 BT48 75 BT49 12.42933314578 BT48 15 BT82 13.79810824578 BT48 18 BT49 15.91910151578 BT48 59 BT51 24.12364445578 BT48 13 BT51 24.14147537578 BT48 189 BT51 26.80170492578 BT48 190 BT51 26.80170492578 BT48 58 BT52 27.95470444578 BT48 43 BT80 28.32651161578 BT48 46 BT52 28.54226644578 BT48 181 BT56 30.82336946578 BT48 186 BT53 31.92369962578 BT48 65 BT71 40.68487732578 BT48 5 BT71 40.91659532578 BT48 187 BT42 42.61219686578 BT48 183 BT43 43.06784521578 BT48 33 BT42 43.11139737578 BT48 42 BT43 45.1397925578 BT48 61 BT74 46.77587542578 BT48 62 BT29 50.94823572578 BT48 55 BT61 50.99844949At present I have an order by clause on distance (ASC). Typically this produces over 20,000 rows - this distance between each job and engineer is calculated. However some jobs and engineers are the same distance apart - the first six results all have the same distance - this is fine. However as this job is located is BT48 I would like the first result to eng_id 50:job_id job_location eng_id eng_location distance578 BT48 50 BT48 0.062985279578 BT48 25 BT47 0.062985279578 BT48 30 BT47 0.062985279578 BT48 51 BT47 0.062985279578 BT48 52 BT47 0.062985279578 BT48 182 BT47 0.062985279Is this possible? Any help would be greatly appreciated.Mark |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-12 : 12:00:16
|
May be this?order by distance,case when eng_location = job_location then 0 else 1 end, eng_id |
 |
|
|
markpirvine
Starting Member
3 Posts |
Posted - 2010-01-12 : 12:06:50
|
| Hi,Thanks for the prompt response - works perfectly!Mark |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-12 : 12:14:28
|
You're welcome  |
|
|
|
|
|
|
Cross Join