 Mysql--how Do I Order By.. |
|
|
|
|
  |
 May 14 2007, 02:44 AM
Post
#1
|
|
|
Newbie [ Level 2 ]  Group: Members Posts: 13 Joined: 12-May 07 Member No.: 21,909  |
well i would like to recieve the results in the same order a supplied in the IN Argument, ho would i do it
Please help CODE SELECT DISTINCT data.id, data.name FROM `data` WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942)
|
 |
 
|
|
May 14 2007, 04:00 AM
Post
#2
|
|
|
Super Member  Group: [HOSTED] Posts: 805 Joined: 8-April 06 From: Lima - Peru Member No.: 12,579 myCENTs:46.87 |
QUOTE(webguru @ May 13 2007, 09:44 PM)  well i would like to recieve the results in the same order a supplied in the IN Argument, ho would i do it Please help CODE SELECT DISTINCT data.id, data.name FROM `data` WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942) To order the results you use the ORDER BY clause of the select statement but i don't know if it is possible to do what you want, I know that is possible to use expressions with the ORDER BY clause and to be honest i dont think this will work but test the following: CODE SELECT DISTINCT data.id, data.name FROM data WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942) ORDER BY 1957,1923,1921,6628,6377,6360,1942 And tell us if it works. Best regards, |
|
|
|
|
May 14 2007, 09:40 AM
Post
#3
|
|
|
Way Out Of Control - You need a life :)  Group: [MODERATOR] Posts: 2,242 Joined: 16-August 05 Member No.: 7,896 myCENTs:44.47 |
I would expect the result to be ordered exactly like the records have been entered in the database, or ordered by (ascending or descending) ID, but proably not ordered in the order you asked.
Maybe a slight workaround would be to add "group by ID". So, you would have all the records for ID=1957 grouped together, all the records for ID=6628 grouped together, etc... Not ordered like you want, however grouped. |
|
|
|
|
May 14 2007, 01:45 PM
Post
#4
|
|
|
Newbie [ Level 2 ] Group: Members Posts: 13 Joined: 12-May 07 Member No.: 21,909 |
well thanks for the reply however it didn't worked
As of Your code I get a error : CODE SQL query: Documentation SELECT DISTINCT data.id, data.name FROM DATA WHERE category =2 AND id IN ( 1957, 1923, 1921, 6628, 6377, 6360, 1942 ) ORDER BY 1957 , 1923, 1921, 6628, 6377, 6360, 1942 MySQL said: Documentation #1054 - Unknown column '1957' in 'order clause' You have assumed them to be columns However what i want to do is: Actually by a algorithm i get the Argument of the IN, however Now i want to filter the results so obtained with a condition that their category is 2 and the results so obtained have to be in the same order as supplied in the IN as they are sorted by a logic So how do i do this |
|
|
|
|
May 14 2007, 05:44 PM
Post
#5
|
|
|
Premium Member Group: Members Posts: 206 Joined: 26-February 07 From: Texas Member No.: 20,598 |
This is how you order by in simple.
CODE SELECT FROM [table name here] [variables like WHERE or *] ORDER BY [object to order by, like date, id, etc] ASC/DESC That's what comes to mind but I might be wrong, to be sure I'd have to look at my code. Read it out loud and it'll make sense. As far as I know you cannot order by numbers like you did, but rather by fields. If I where you I'd try and write that simpler. I will figure out an answer if I can to your question and edit my post with it 
This post has been edited by SilverFox: May 14 2007, 05:46 PM |
|
|
|
|
May 14 2007, 08:41 PM
Post
#6
|
|
|
Member [ Level 2 ] Group: Members Posts: 71 Joined: 16-December 06 Member No.: 18,419 |
It would probably be best to just order them using a PHP script or whatever if that's all you want. I'm not sure if MySQL is capable of that.
|
|
|
|
|
May 15 2007, 02:33 AM
Post
#7
|
|
|
Member - Active Contributor Group: Members Posts: 88 Joined: 5-April 07 From: Cusco - Peru Member No.: 21,283 |
Hi webguru!
As order BY said, you need a "BY", some criteria to make an alphanumeric sorting. Assuming that your "IN" list is generated by PHP, you only have to add a sub-query containing the IDs and their respective order. Look at this: CODE SELECT tR.* FROM (SELECT DISTINCT data.id, data.name FROM `data` WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942)) as tR INNER JOIN ( -- generated by PHP >> SELECT 1957 as id, 1 as criteria UNION SELECT 1923 as id, 2 as criteria UNION . . . -- << generated by PHP ) as tOrder ON tR.id = tOrder.id ORDER by tOrder.criteria Blessings!! |
|
|
|
|
May 15 2007, 05:18 AM
Post
#8
|
|
|
Newbie [ Level 2 ] Group: Members Posts: 13 Joined: 12-May 07 Member No.: 21,909 |
Well thanks Everybody For the reply,
I found The solution on the mysql dev forums.. well its the following - We need to specify a Field Tag in the Order By Argument with its first element to ne the column id CODE SELECT DISTINCT data.id, data.name
FROM data WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942) ORDER BY FIELD(id,1957,1923,1921,6628,6377,6360,1942) |
|
|
|
|
May 16 2007, 04:29 AM
Post
#9
|
|
|
Super Member Group: [HOSTED] Posts: 805 Joined: 8-April 06 From: Lima - Peru Member No.: 12,579 myCENTs:46.87 |
QUOTE(webguru @ May 15 2007, 12:18 AM) Well thanks Everybody For the reply, I found The solution on the mysql dev forums.. well its the following - We need to specify a Field Tag in the Order By Argument with its first element to ne the column id CODE SELECT DISTINCT data.id, data.name FROM data WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942) ORDER BY FIELD(id,1957,1923,1921,6628,6377,6360,1942) Well i'm not too far for the solution, and as i say in my previous post, you can use any expression with the ORDER BY clause, in this case, you are using the FIELD function. Every day we know something new. Best regards, |
|
|
|
|
May 16 2007, 08:45 AM
Post
#10
|
|
|
Way Out Of Control - You need a life :) Group: [MODERATOR] Posts: 2,242 Joined: 16-August 05 Member No.: 7,896 myCENTs:44.47 |
QUOTE Every day we know something new. That's exactly what I think. However, I would rather express it slightly differently. I would like to be able, everyday, to learn something new. When I will not any more be able to learn something different, this will mean I am too old. When I will know that I know everythin, no need to learn something new, this will mean I am getting stupid. Hope that I will keep learning new things many years from now. |
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 4th December 2008 - 11:21 PM |