Jump to content
xisto Community
webguru

Mysql--how Do I Order By..

Recommended Posts

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) 

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 B)
Edited by SilverFox (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 by vhortex (see edit history)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.