Perhaps case is the way to go, I'll look into that this morning. Unfortunately there are three groupings. So my IF or CASE needs to check for example:
if timezone = 3,5,6,7 then 1 if timezone = 1,2,4 then 2 if timezone = 8,9 then 3 So, it's a bit more complicated than I tohught it would be. I originally wanted to use mySQL as the part that did this processing as opposed to outputting the results then sorting an array. Jeff From: 卢钧轶 [mailto:cenal...@gmail.com] Sent: Sunday, October 25, 2009 1:19 AM To: Jeff Subject: Re: Using IF in a query to set a variable then sort on said variable Hi jeff If there's only two Candidate value for @tempzone, you can accomplish you goal like this : Select xxx from tbl_name d order by if( d.timezone in (1,3,5,8), 1 , 0); 2009/10/23 Jeff <j...@platinumsynergy.com<mailto:j...@platinumsynergy.com>> I currently have a query like so: SELECT p.fldId, p.fldFName, p.fldLName, p.fldEmail, p.fldPhone, p.resellerId, d.timezoneId, d.bestTime, d.lastDate, d.lastTime, d.lastConnected FROM tblProspects as p LEFT JOIN tblProspectsDetails as d ON d.prospectId = p.fldId WHERE p.uId = "46437" ORDER BY d.lastDate ASC, d.timezoneId DESC Basically I have been trying several ways to get this set up to work but due to the way the timezones are set up in this system, a regular sort won't work. What I want to know is, is it possible to check the value of field and set a variable, then sort on that. For example: IF d.timezone = 5 THEN @tempzone = 1 Then sort the entire query on that tempzone. Basically what I want to do is test the timezone ids versus a few numbers, assign them a value and sort on that. IF d.timezone = (1,3,5,8) THEN @tempzone = 1 (pseudo code) Is this possible in a mySQL query directly? The only other option I guess I have is to run a mass update on a field then doing the normal order but doing three updates just to make one select work seems kind of unfortunate :( Jeff
