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

--_000_12F8CFCBD7053D438A5FD5828E3357BF0141951F322FEXVMBX016 5e_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I currently have a query like so:

SELECT p.fldId, p.fldFName, p.fldLName, p.fldEmail, p.fldPhone, p.resellerI=
d, d.timezoneId, d.bestTime, d.lastDate, d.lastTime, d.lastConnected FROM t=
blProspects as p LEFT JOIN tblProspectsDetails as d ON d.prospectId =3D p.f=
ldId WHERE p.uId =3D "46437" ORDER BY d.lastDate ASC, d.timezoneId DESC

Basically I have been trying several ways to get this set up to work but du=
e 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 =3D 5 THEN @tempzone =3D 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 o=
n that.

IF d.timezone =3D (1,3,5,8) THEN @tempzone =3D 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 do=
ing three updates just to make one select work seems kind of unfortunate :(

Jeff

--_000_12F8CFCBD7053D438A5FD5828E3357BF0141951F322FEXVMBX016 5e_--

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

2009/10/23 Jeff :
> I currently have a query like so:
>
> SELECT p.fldId, p.fldFName, p.fldLName, p.fldEmail, p.fldPhone, p.reselle=
rId, d.timezoneId, d.bestTime, d.lastDate, d.lastTime, d.lastConnected FROM=
tblProspects as p LEFT JOIN tblProspectsDetails as d ON d.prospectId =3D p=
..fldId WHERE p.uId =3D "46437" ORDER BY d.lastDate ASC, d.timezoneId DESC

You do not need to set a variable, just 'ORDER BY FIELD(timezoneId,
...., ..., ...)' :
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=3Dgcdmg-mysql-2@m.gmane.o rg

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

--_000_12F8CFCBD7053D438A5FD5828E3357BF0141951F32A1EXVMBX016 5e_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: base64

UGVyaGFwcyBjYXNlIGlzIHRoZSB3YXkgdG8gZ28sIEknbGwgbG9vayBpbnRv IHRoYXQgdGhpcyBt
b3JuaW5nLg0KDQpVbmZvcnR1bmF0ZWx5IHRoZXJlIGFyZSB0aHJlZSBncm91 cGluZ3MuIFNvIG15
IElGIG9yIENBU0UgbmVlZHMgdG8gY2hlY2sgZm9yIGV4YW1wbGU6DQoNCmlm IHRpbWV6b25lID0g
Myw1LDYsNyB0aGVuIDENCmlmIHRpbWV6b25lID0gMSwyLDQgdGhlbiAyDQpp ZiB0aW1lem9uZSA9
IDgsOSB0aGVuIDMNCg0KU28sIGl0J3MgYSBiaXQgbW9yZSBjb21wbGljYXRl ZCB0aGFuIEkgdG9o
dWdodCBpdCB3b3VsZCBiZS4gSSBvcmlnaW5hbGx5IHdhbnRlZCB0byB1c2Ug bXlTUUwgYXMgdGhl
IHBhcnQgdGhhdCBkaWQgdGhpcyBwcm9jZXNzaW5nIGFzIG9wcG9zZWQgdG8g b3V0cHV0dGluZyB0
aGUgcmVzdWx0cyB0aGVuIHNvcnRpbmcgYW4gYXJyYXkuDQoNCkplZmYNCg0K RnJvbTogwqy+++nz
IFttYWlsdG86Y2VuYWx1bHVAZ21haWwuY29tXQ0KU2VudDogU3VuZGF5LCBP Y3RvYmVyIDI1LCAy
MDA5IDE6MTkgQU0NClRvOiBKZWZmDQpTdWJqZWN0OiBSZTogVXNpbmcgSUYg aW4gYSBxdWVyeSB0
byBzZXQgYSB2YXJpYWJsZSB0aGVuIHNvcnQgb24gc2FpZCB2YXJpYWJsZQ0K DQpIaSBqZWZmDQoN
CklmIHRoZXJlJ3Mgb25seSB0d28gQ2FuZGlkYXRlIHZhbHVlIGZvciBAdGVt cHpvbmUsIHlvdSBj
YW4gYWNjb21wbGlzaCB5b3UgZ29hbCBsaWtlIHRoaXMgOg0KDQogICAgU2Vs ZWN0IHh4eCBmcm9t
IHRibF9uYW1lIGQgb3JkZXIgYnkgaWYoIGQudGltZXpvbmUgaW4gKDEsMyw1 LDgpLCAxICwgMCk7
DQoNCg0KMjAwOS8xMC8yMyBKZWZmIDxKZWZmQHBsYXRpbnVtc3luZXJneS5j b208bWFpbHRvOkpl
ZmZAcGxhdGludW1zeW5lcmd5LmNvbT4+DQpJIGN1cnJlbnRseSBoYXZlIGEg cXVlcnkgbGlrZSBz
bzoNCg0KU0VMRUNUIHAuZmxkSWQsIHAuZmxkRk5hbWUsIHAuZmxkTE5hbWUs IHAuZmxkRW1haWws
IHAuZmxkUGhvbmUsIHAucmVzZWxsZXJJZCwgZC50aW1lem9uZUlkLCBkLmJl c3RUaW1lLCBkLmxh
c3REYXRlLCBkLmxhc3RUaW1lLCBkLmxhc3RDb25uZWN0ZWQgRlJPTSB0YmxQ cm9zcGVjdHMgYXMg
cCBMRUZUIEpPSU4gdGJsUHJvc3BlY3RzRGV0YWlscyBhcyBkIE9OIGQucHJv c3BlY3RJZCA9IHAu
ZmxkSWQgV0hFUkUgcC51SWQgPSAiNDY0MzciIE9SREVSIEJZIGQubGFzdERh dGUgQVNDLCBkLnRp
bWV6b25lSWQgREVTQw0KDQpCYXNpY2FsbHkgSSBoYXZlIGJlZW4gdHJ5aW5n IHNldmVyYWwgd2F5
cyB0byBnZXQgdGhpcyBzZXQgdXAgdG8gd29yayBidXQgZHVlIHRvIHRoZSB3 YXkgdGhlIHRpbWV6
b25lcyBhcmUgc2V0IHVwIGluIHRoaXMgc3lzdGVtLCBhIHJlZ3VsYXIgc29y dCB3b24ndCB3b3Jr
Lg0KDQpXaGF0IEkgd2FudCB0byBrbm93IGlzLCBpcyBpdCBwb3NzaWJsZSB0 byBjaGVjayB0aGUg
dmFsdWUgb2YgIGZpZWxkIGFuZCBzZXQgYSB2YXJpYWJsZSwgdGhlbiBzb3J0 IG9uIHRoYXQuIEZv
ciBleGFtcGxlOg0KDQpJRiBkLnRpbWV6b25lID0gNSBUSEVOIEB0ZW1wem9u ZSA9IDENCg0KVGhl
biBzb3J0IHRoZSBlbnRpcmUgcXVlcnkgb24gdGhhdCB0ZW1wem9uZS4gQmFz aWNhbGx5IHdoYXQg
SSB3YW50IHRvIGRvIGlzIHRlc3QgdGhlIHRpbWV6b25lIGlkcyB2ZXJzdXMg YSBmZXcgbnVtYmVy
cywgYXNzaWduIHRoZW0gYSB2YWx1ZSBhbmQgc29ydCBvbiB0aGF0Lg0KDQpJ RiBkLnRpbWV6b25l
ID0gKDEsMyw1LDgpIFRIRU4gQHRlbXB6b25lID0gMSAocHNldWRvIGNvZGUp DQoNCklzIHRoaXMg
cG9zc2libGUgaW4gYSBteVNRTCBxdWVyeSBkaXJlY3RseT8gVGhlIG9ubHkg b3RoZXIgb3B0aW9u
IEkgZ3Vlc3MgSSBoYXZlIGlzIHRvIHJ1biBhIG1hc3MgdXBkYXRlIG9uIGEg ZmllbGQgdGhlbiBk
b2luZyB0aGUgbm9ybWFsIG9yZGVyIGJ1dCBkb2luZyB0aHJlZSB1cGRhdGVz IGp1c3QgdG8gbWFr
ZSBvbmUgc2VsZWN0IHdvcmsgc2VlbXMga2luZCBvZiB1bmZvcnR1bmF0ZSA6 KA0KDQpKZWZmDQoN
Cg==

--_000_12F8CFCBD7053D438A5FD5828E3357BF0141951F32A1EXVMBX016 5e_--

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


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

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

QW4gYWRkaXRpb25hbCBmaWVsZCBpcyBwcmV0dHkgbXVjaCBpbXBvc3NpYmxl IHNpbmNlIHRoYXQg
Y2FzZSB3b3VsZCBjaGFuZ2UgdGhyb3VnaG91dCB0aGUgZGF5IHVuZm9ydHVu YXRlbHkuIEJ1dCB0
aGFuayB5b3UgYWxsIGZvciB5b3VyIGFzc2lzdGFuY2UsIHdpbGwgZG8gc29t ZSB0ZXN0aW5nLg0K
DQpKZWZmDQoNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBK YWltZSBDcmVzcG8g
UmluY8OzbiBbbWFpbHRvOmpjcmVzcG9Ad2FycC5lc10gDQpTZW50OiBNb25k YXksIE9jdG9iZXIg
MjYsIDIwMDkgMTI6NTIgUE0NClRvOiBKZWZmDQpDYzogbXlzcWxAbGlzdHMu bXlzcWwuY29tDQpT
dWJqZWN0OiBSZTogVXNpbmcgSUYgaW4gYSBxdWVyeSB0byBzZXQgYSB2YXJp YWJsZSB0aGVuIHNv
cnQgb24gc2FpZCB2YXJpYWJsZQ0KDQoyMDA5LzEwLzI2IEplZmYgPEplZmZA cGxhdGludW1zeW5l
cmd5LmNvbT46DQo+IFBlcmhhcHMgY2FzZSBpcyB0aGUgd2F5IHRvIGdvLCBJ J2xsIGxvb2sgaW50
byB0aGF0IHRoaXMgbW9ybmluZy4NCj4NCj4gVW5mb3J0dW5hdGVseSB0aGVy ZSBhcmUgdGhyZWUg
Z3JvdXBpbmdzLiBTbyBteSBJRiBvciBDQVNFIG5lZWRzIHRvIGNoZWNrIGZv ciBleGFtcGxlOg0K
Pg0KPiBpZiB0aW1lem9uZSA9IDMsNSw2LDcgdGhlbiAxDQo+IGlmIHRpbWV6 b25lID0gMSwyLDQg
dGhlbiAyDQo+IGlmIHRpbWV6b25lID0gOCw5IHRoZW4gMw0KDQpZZXMsIENh c2UgZnVuY3Rpb24g
aXMgdGhlIHdheSB0byBnby4NCg0KQW55d2F5LCBjaGVjayBmb3IgcGVyZm9y bWFuY2UgaXNzdWVz
OiBpbiB0aGF0IGNhc2UsIHByZWNhbGN1bGF0aW5nIGFuZA0Kc3RvcmluZyBh biBhZGl0aW9uYWwg
ZmllbGQgd291bGQgYmUgdGhlIGJlc3Qgd2F5IChpdCBjb3VsZCBiZSBkb25l DQp3aXRoIGEgdHJp
Z2dlciwgZm9yIGV4YW1wbGUpLg0KDQoNCi0tIA0KSmFpbWUgQ3Jlc3BvDQpN eVNRTCAmIEphdmEg
SW5zdHJ1Y3Rvcg0KV2FycCBOZXR3b3Jrcw0KPGh0dHA6Ly93YXJwLmVzPg0K