Mysql--how Do I Order By..

[webguru 0]

well i would like to recieve the results in the same order a supplied in the IN Argument, ho would i do itPlease help

SELECT DISTINCT data.id, data.name FROM `data` WHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942) 

[TavoxPeru 0]

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

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:

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,

[yordan 10]

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.

[webguru 0]

well thanks for the reply however it didn't worked As of Your code I get a error :

SQL query: DocumentationSELECT DISTINCT data.id, data.nameFROM DATAWHERE category =2AND idIN ( 1957, 1923, 1921, 6628, 6377, 6360, 1942 )ORDER BY 1957 , 1923, 1921, 6628, 6377, 6360, 1942MySQL 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 logicSo how do i do this

[SilverFox1405241541 0]

This is how you order by in simple.

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
Edited May 14, 2007 by SilverFox (see edit history)

[bluefish1405241537 0]

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.

[develCuy 0]

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:

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

[webguru 0]

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

SELECT DISTINCT data.id, data.nameFROM dataWHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942)ORDER BY FIELD(id,1957,1923,1921,6628,6377,6360,1942)

[TavoxPeru 0]

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

SELECT DISTINCT data.id, data.nameFROM dataWHERE 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,

[yordan 10]

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.

[vhortex 1]

SELECT DISTINCT data.id, data.nameFROM dataWHERE category = 2 AND id IN(1957,1923,1921,6628,6377,6360,1942)ORDER BY FIELD(id,1957,1923,1921,6628,6377,6360,1942)

Thanks for sharing this. I have been coding and doing database in mySQL for such a long time but never have known that this is possible. Nice share.Note Edit: Quote was snipped for easy reading.

Edited May 17, 2007 by vhortex (see edit history)