Help with ORDER BY

--0016e6d97745775dd1049bb51c2a
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d97745775dd1049bb51c2a--

RE: Help with ORDER BY

SELECT name, city, state, phone, prods_done, cancels, miles FROM
(SELECT name, city, state, phone, prods_done, cancels, miles, ((prod_done -=
cancels) * 100 / prod_done) reliability
FROM volunteer_search WHERE project_id =3D 5653) A
ORDER BY reliability DESC, miles ASC

Give it a try !!!

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Richard Reina [mailto:gatorreina@gmail.com]=20
Sent: Monday, February 07, 2011 1:08 PM
To: mysql@lists.mysql.com
Subject: Help with ORDER BY

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 =3D 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg