RE: Using IF in a query to set a variable then sort on said variable

[Jeff]

An additional field is pretty much impossible since that case would change 
throughout the day unfortunately. But thank you all for your assistance, will 
do some testing.

Jeff

-Original Message-
From: Jaime Crespo Rincón [mailto:jcre...@warp.es] 
Sent: Monday, October 26, 2009 12:52 PM
To: Jeff
Cc: mysql@lists.mysql.com
Subject: Re: Using IF in a query to set a variable then sort on said variable

2009/10/26 Jeff :
> 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

Yes, Case function is the way to go.

Anyway, check for performance issues: in that case, precalculating and
storing an aditional field would be the best way (it could be done
with a trigger, for example).


-- 
Jaime Crespo
MySQL & Java Instructor
Warp Networks
<http://warp.es>

Re: Using IF in a query to set a variable then sort on said variable

[Jaime Crespo Rincón]

2009/10/26 Jeff :
> 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

Yes, Case function is the way to go.

Anyway, check for performance issues: in that case, precalculating and
storing an aditional field would be the best way (it could be done
with a trigger, for example).


-- 
Jaime Crespo
MySQL & Java Instructor
Warp Networks


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Using IF in a query to set a variable then sort on said variable

[Jeff]

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 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

Re: Using IF in a query to set a variable then sort on said variable

[Jaime Crespo Rincón]

2009/10/23 Jeff :
> 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

You do not need to set a variable, just 'ORDER BY FIELD(timezoneId,
..., ..., ...)' :
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field>

You could also use the IF() or CASE functions to achieve similar results.
--
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Using IF in a query to set a variable then sort on said variable

[Jeff]

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