Re: Ordering an IN query

[Perrin Harkins]

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

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=arch...@jab.org

RE: Ordering an IN query

[Martin Gainty]

Aaron-

to reorder results of a column specify 
FIELD(ColumnName,1stPosition,2ndPosition) e.g.

mysql>use information_schema;
mysql> select ordinal_position,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
 from COLUMNS WHERE ORDINAL_POSITION=23 OR ORDINAL_POSITION=18 ORDER BY 
FIELD(ORDINAL_POSITION,23,18);

Shalom
Martin Gainty 
__ 
Note de déni et de confidentialité
 Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> From: afisc...@smith.edu
> To: mysql@lists.mysql.com
> Subject: Ordering an IN query
> Date: Fri, 5 Jun 2009 09:09:24 -0400
> 
> 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=mgai...@hotmail.com
> 

_
Hotmail® has ever-growing storage! Don’t worry about storage limits. 
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009

Ordering an IN query

[Aaron Fischer]

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=arch...@jab.org