ORDER BY FIELD() doesn't 1

[OsakaWebbie]

My first time using ORDER BY FIELD(), and it seems pretty simple, but it's not working for me. The value of FIELD() is correct, but in the ORDER BY location, it seems to have no effect on sorting. After simplifying to try to find the problem, here is my diagnostic query:

SELECT PersonID, FIELD(PersonID,2957,5,3883,3292,713,1248,2971,3538) FROM person WHERE PersonID IN (2957,5,3883,3292,713,1248,2971,3538) ORDER BY FIELD(PersonID,2957,5,3883,3292,713,1248,2971,3538)

The results come in like this (PersonID is an autoincrement integer field that is the primary key):

5       2
713     5
1248    6
2957    1
2971    7
3292    4
3538    8
3883    3

So FIELD() seems to be working, but why isn't the result set getting sorted by it?

 

[towerbase]

I've not come across the FIELD function before. It seems as though it could be useful.

I set up a small test and found that the ORDER BY FIELD(... function works okay for me. I am using 5.1.40-community.



Andrew

 

[feherke]

Hi

It works for me. I suppose PersonID is integer, right ? And the [tt]field()[/tt] function is the original by MySQL ? How you executed that query ? ( I used [tt]mysql[/tt], the MySQL command-line tool. )

By the way, note that the [tt]order by[/tt] clause also accepts numbers and aliases. The following two variants are equivalent with yours but easier to read and maintain :

[b]SELECT[/b] PersonID[teal],[/teal] [b]FIELD[/b][teal]([/teal]PersonID[teal],[/teal][purple]2957[/purple][teal],[/teal][purple]5[/purple][teal],[/teal][purple]3883[/purple][teal],[/teal][purple]3292[/purple][teal],[/teal][purple]713[/purple][teal],[/teal][purple]1248[/purple][teal],[/teal][purple]2971[/purple][teal],[/teal][purple]3538[/purple][teal])[/teal] [b]FROM[/b] person [b]WHERE[/b] PersonID [b]IN[/b] [teal]([/teal][purple]2957[/purple][teal],[/teal][purple]5[/purple][teal],[/teal][purple]3883[/purple][teal],[/teal][purple]3292[/purple][teal],[/teal][purple]713[/purple][teal],[/teal][purple]1248[/purple][teal],[/teal][purple]2971[/purple][teal],[/teal][purple]3538[/purple][teal])[/teal] [b]ORDER[/b] [b]BY[/b] [highlight][purple]2[/purple][/highlight][teal];[/teal]

[b]SELECT[/b] PersonID[teal],[/teal] [b]FIELD[/b][teal]([/teal]PersonID[teal],[/teal][purple]2957[/purple][teal],[/teal][purple]5[/purple][teal],[/teal][purple]3883[/purple][teal],[/teal][purple]3292[/purple][teal],[/teal][purple]713[/purple][teal],[/teal][purple]1248[/purple][teal],[/teal][purple]2971[/purple][teal],[/teal][purple]3538[/purple][teal])[/teal] [highlight]foo[/highlight] [b]FROM[/b] person [b]WHERE[/b] PersonID [b]IN[/b] [teal]([/teal][purple]2957[/purple][teal],[/teal][purple]5[/purple][teal],[/teal][purple]3883[/purple][teal],[/teal][purple]3292[/purple][teal],[/teal][purple]713[/purple][teal],[/teal][purple]1248[/purple][teal],[/teal][purple]2971[/purple][teal],[/teal][purple]3538[/purple][teal])[/teal] [b]ORDER[/b] [b]BY[/b] [highlight]foo[/highlight][teal];[/teal]

And of course the above variants also work for me.


Feherke.

http://free.rootshell.be/~feherke/

 

[OsakaWebbie]

Aha! You asked me how I was executing the query - that was an excellent question. I don't have access to the command line, but I decided to try it in phpMyAdmin instead of just the freeform query page that is part of the application I wrote and am continuing to develop. It worked in phpMyAdmin! So then I took a look at the code for my freeform query page and found the problem. It's a very simple piece of code, but I use a little jQuery plugin to allow sorting of the table of results AFTER it is displayed, and I had been doing a bit of parsing of the SQL to try to determine which field was initially sorted (if it doesn't find any result set field in the ORDER BY, it isn't supposed to do anything). But it apparently picked up the "PersonID" text inside the function and decided that was the sort field (and the plugin actually took action and sorted it - I didn't know it would do that!). I commented out that section of code and all is well.

I was only using the freeform page to double check that the query was working before I use it in another part of my code. It will probably work fine in the place I plan to use it, because there is no such parsing of the query to guess the sorting.

Thanks for your help, and sorry to have bothered you regarding what turned out to be a weakness in my own code...

 

[OsakaWebbie]

Oh, and by the way, the FIELD() function was only repeated in the result set for troubleshooting this problem - in fact, PersonID isn't involved at all in the results I'm after. I was just using both of them for this diagnostics query. That's why the list appeared three times.

Is it true, however, that I do need the list of IDs in both the WHERE clause and ORDER BY FIELD? Sometimes the list of IDs will be long, so if there is a trick to only have to include it once, it would speed things up (I'm sending the query by AJAX and would rather not have to have special code to assemble the query).