List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:February 7 2011 6:07pm
Subject:Help with ORDER BY
View as plain text  
I currently have a query that organizes search results for volunteers that
should be called for projects based on how close they live to a project the
and there past attendance.

Currently doing

"SELECT name, city, state, phone, prods_done, cancels, miles
FROM volunteer_search
WHERE  project_id = 5653
ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC";

The results look something like this:

Jim Barnes Chicago, IL 773-555-7777   2  1  11.5
Kelley Smith Cicero, IL 708-444-2121   3  0  21.6
Kim Ayers Plainfield, IL 630-888-9898 22 1  25.1

I am trying to find a way to give more weight to reliability (projects done
and cancels).

For example the fact that Kim Ayers has done 22 projects makes her more
desirable than either Jim Barnes and Kelley Smith even though she has 1
cancel is farther from the project than both the other candidates.  The
desired order would be:

Kim Ayers Plainfield, IL 630-888-9898 22 1  25.1
Kelley Smith Cicero, IL 708-444-2121   3  0  21.6
Jim Barnes Chicago, IL 773-555-7777   2  1  11.5

Can the query could be modified to take into account such considerations?  I
realize that I have not really quantified how much reliability and the
number of projects done trumps distance from the project, but in the
beginning I am willing to set that somewhat arbitrary and adjust it later.
I realize that this may be beyond the scope of a MySQL query.  If so I am
grateful for any ideas on where to go for info/help.

Thanks,

Richard

Thread
Help with ORDER BYRichard Reina7 Feb
  • RE: Help with ORDER BYRolando Edwards7 Feb