Ordering an IN query

I have a query that I build dynamically, here is an example:

select from (table1 as t1 left join table2 as t2 on t1.id = t2.id)
left join table3 as t3 on t1.id = t3.id where t1.id in ('221593',
'221591', 'CC3762', 'CC0059')

So I build the query in the order that I want it displayed. That is
display 221593, then 221591, then CC3762, etc. However, when the
query is executed it looks like it automatically sorts the codes in
ascending order, so I get 221591, then 221593, the CC0059, etc.

I want the results displayed in the order that I build the query. Is
there some way of doing that?

Thanks,

-Aaron

--
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: Ordering an IN query

--_f477d9f0-6f51-469e-9e14-008a309f319a_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


Aaron-

to reorder results of a column specify FIELD(ColumnName=2C1stPosition=2C2nd=
Position) e.g.

mysql>use information_schema=3B
mysql> select ordinal_position=2CTABLE_CATALOG=2CTABLE_SCHEMA=2CTABLE_NAME =
=2CCOLUMN_NAME
from COLUMNS WHERE ORDINAL_POSITION=3D23 OR ORDINAL_POSITION=3D18 ORDER BY=
FIELD(ORDINAL_POSITION=2C23=2C18)=3B

Shalom
Martin Gainty=20
______________________________________________=20
Note de d=E9ni et de confidentialit=E9
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAte=
s pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sa=
tisfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9=
e ou la copie de ceci est interdite. Ce message sert =E0 l'information seul=
ement et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant =
donn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..




> From: afischer@smith.edu
> To: mysql@lists.mysql.com
> Subject: Ordering an IN query
> Date: Fri=2C 5 Jun 2009 09:09:24 -0400
>=20
> I have a query that I build dynamically=2C here is an example:
>=20
> select from (table1 as t1 left join table2 as t2 on t1.id =3D t2.id) =20
> left join table3 as t3 on t1.id =3D t3.id where t1.id in ('221593'=2C =20
> '221591'=2C 'CC3762'=2C 'CC0059')
>=20
> So I build the query in the order that I want it displayed. That is =20
> display 221593=2C then 221591=2C then CC3762=2C etc. However=2C when the=
=20
> query is executed it looks like it automatically sorts the codes in =20
> ascending order=2C so I get 221591=2C then 221593=2C the CC0059=2C etc.
>=20
> I want the results displayed in the order that I build the query. Is =20
> there some way of doing that?
>=20
> Thanks=2C
>=20
> -Aaron
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20

____________________________________________________________ _____
Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=20
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_T AGLM_WL_HM_Tuto=
rial_Storage_062009=

--_f477d9f0-6f51-469e-9e14-008a309f319a_--

Re: Ordering an IN query

On Fri, Jun 5, 2009 at 9:09 AM, Aaron Fischer wrote:
> So I build the query in the order that I want it displayed. =A0That is di=
splay
> 221593, then 221591, then CC3762, etc. =A0However, when the query is exec=
uted
> it looks like it automatically sorts the codes in ascending order, so I g=
et
> 221591, then 221593, the CC0059, etc.
>
> I want the results displayed in the order that I build the query. =A0Is t=
here
> some way of doing that?

You'll have to use a function like CASE() or FIND_IN_SET() to map the
IDs to values in an ORDER BY clause.

- Perrin

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