Sorting by a calculated field

# Thread: Sorting by a calculated field

1. Mike Preuett Guest

## Sorting by a calculated field

I&#039;ve got the following code snip from a project I&#039;m working on:<BR><BR>Response.Write "&#060;CENTER&#062;Country Stores within " & Radius & " miles of ZipCode " & zip1 & "&#060;/CENTER&#062;"<BR>Response.Write "&#060;TABLE BORDER=1 CELLPADDING=6 CELLSPACING=1 WIDTH=730 align=center&#062;"<BR> Response.Write "&#060;TR&#062;"<BR> Response.Write "&#060;TD align=center&#062;ZipCode&#060;/TD&#062;"<BR> Response.Write "&#060;TD align=center&#062;City&#060;/TD&#062;"<BR> Response.Write "&#060;TD align=center&#062;Distance (Miles)&#060;/TD&#062;" <BR> Response.Write "&#060;/TR&#062;"<BR> while not ZP2.EOF<BR> &#039;Calculate mileage using V & H formula from telco<BR> If ZP1("zip") = ZP2("zip") Then<BR> miles = 0<BR> Else<BR> miles=SQR(.1*((ZP1("V")-ZP2("V"))^2 + (ZP1("H")-ZP2("H"))^2))<BR> end if<BR> if miles &#060;= csng(radius) then<BR> Response.Write "&#060;TR&#062;"<BR> Response.Write "&#060;TD align=center&#062;" & ZP2("Zip") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD align=center&#062;" & ZP2("Location") & "&#060;/TD&#062;"<BR> Response.Write "&#060;TD align=center&#062;" & FormatNumber(miles, 2) & "&#060;/TD&#062;"<BR> Response.Write "&#060;/TR&#062;"<BR> end if<BR> ZP2.movenext<BR> wend<BR><BR>The distance is generated on the fly as I loop through the recordset. Any suggestions on how to order this output by the calculated distance?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Do it in SQL...

See my answer to "Help with sorting a recordset (Bradley)", below.<BR><BR>You could even reject the records outside the radius in SQL! Probably perform better than the VBS code, too.<BR><BR>

3. Mike Preuett Guest

## RE: Do it in SQL...

That&#039;s what I&#039;ve been thinking of doing. I did figure a way with my select to limit to only the radius I want. I was thinking of writing a temp table with the results that includes mileage and just ordering by mileage in that table and then dropping it.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Why the temp table???

If you will immediately drop it, then just return the results directly and be happy.<BR><BR>

5. Mike Preuett Guest

## RE: Why the temp table???

That&#039;s true. I&#039;m really not keeping or doing anything other than returning the data.

6. Mike Preuett Guest

## RE: Do it in SQL...

Would I need to use a cursor in SQL to do this? I tried a couple things this morning and couldn&#039;t get the distance returned.

7. Senior Member
Join Date
Dec 1969
Posts
96,118