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]

On 9-Apr-2004, Constantine Kakoushis <ti************@dsl.pipex.com> wrote:

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.

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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)