Order results by derived value

[Constantine Kakoushis]

Hi,

I'm looking for the most efficient way of displaying results from an SQL
query that must be ordered by a value that is derived following retrieval.

The user enters their location (postal code) and a search term (retailer
type). I can then query the DB for matching retailers and return their
geographic grid position. Based on this I can calculate the distance
between the user's location and the retailer. I'd like to display the
results ordered by the calculated distance. There could be hundreds of
matches so I'd also like to distribute the results over a number of pages
e.g. 1-10 on page 1, 11-20 on page 2, etc.

I have considered reading the data into a 2d array and sorting by distance
but distributing the results over many pages might be more difficult. I've
also considered creating a DB view but I'm not sure how to do this in
MySQL. I don't want to kill the server either!

This must have been solved many times by various people - I'd just like an
indication of which might be the best solution to avoid pursuing a deadend.

Tino.

[Tom Thackrey]

Re: Order results by derived value


On 9-Apr-2004, Constantine Kakoushis <tino.kakoushis @dsl.pipex.com> wrote:
[color=blue]
> I'm looking for the most efficient way of displaying results from an SQL
> query that must be ordered by a value that is derived following retrieval.
>
> The user enters their location (postal code) and a search term (retailer
> type). I can then query the DB for matching retailers and return their
> geographic grid position. Based on this I can calculate the distance
> between the user's location and the retailer. I'd like to display the
> results ordered by the calculated distance. There could be hundreds of
> matches so I'd also like to distribute the results over a number of pages
> e.g. 1-10 on page 1, 11-20 on page 2, etc.
>
> I have considered reading the data into a 2d array and sorting by distance
> but distributing the results over many pages might be more difficult. I've
> also considered creating a DB view but I'm not sure how to do this in
> MySQL. I don't want to kill the server either!
>
> This must have been solved many times by various people - I'd just like an
> indication of which might be the best solution to avoid pursuing a
> deadend.[/color]

The simplest way to do this is to compute the distance with sql: "select
(distance computation) as distance,* from ... order by distance limit 1,10".
If you don't need the distance for anything but sorting you can "select *
from ... order by (distance computation) limit 1,10". You probably want to
ensure that the columns used by the distance computation are indexed.

--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)