| Author |
Topic |
|
satish.gorijala
Posting Yak Master
182 Posts |
 Posted - 2009-03-31 : 01:35:33
|
| Hi, i have following data in table. Based on MZip Distance, i need to find out the CZIP Which is nearer to MZIP.For Example, in the below data, the first row shows MZIP data 501 have distance '0'. Now i need to find out the CZIP which is nearest distance to '0'. Need to check all rows start with 501. Finally i will get result as 544 from CZIP.MZIP CZIP DISTANCE501 501 0501 544 0501 11742 0.32501 11738 1.5501 11741 1.66605 605 0605 603 2.58605 604 4.16605 602 5.47611 611 0611 685 2.14611 670 3.37611 669 6.71613 613 0613 614 0613 616 5.6613 659 6.61613 612 6.84The Final result is like thisMZIP CZIP Distance501 544 0605 603 2.58611 685 2.14613 614 0G. Satish |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-31 : 02:48:01
|
| [code]declare @t table(MZIP int, CZIP int,DISTANCE decimal(18,2))insert into @t select 501, 501, 0insert into @t select 501, 544, 0insert into @t select 501, 11742, 0.32insert into @t select 501, 11738, 1.5insert into @t select 501, 11741, 1.66insert into @t select 605, 605, 0insert into @t select 605, 603, 2.58insert into @t select 605, 604, 4.16insert into @t select 605, 602, 5.47insert into @t select 611, 611, 0insert into @t select 611, 685, 2.14insert into @t select 611, 670, 3.37insert into @t select 611, 669, 6.71insert into @t select 613, 613, 0insert into @t select 613, 614, 0insert into @t select 613, 616, 5.6insert into @t select 613, 659, 6.61insert into @t select 613, 612, 6.84select MZIP , CZIP ,DISTANCE from (select row_number()over(partition by mzip order by mzip) as rid , * from @t) s where rid = 2[/code] |
 |
|
|
|
|
|
Typical requirement