Handy MySQL - ORDER BY FIELD

My problem was I wanted to sort properties by status and date added but needed the status to show in a priority fashion. MYSQL ORDER BY FIELD() took care of it

2007-02-08 17:49:00.0

Handy MySQL - ORDER BY FIELD

Had a slight problem today and found a neat solution.

My problem was I wanted to sort properties by status and date added but needed the status to show in a priority fashion.

For example, I wanted the order of the property status to be For Sale, For Rent, Sale Agreed and also to sort by date added.

I couldnt use ORDER BY propertyStatus, propertyDateAdded because then For Rent would come before For Sale.

The solution was to use ORDER BY FIELD

Syntax: ORDER BY FIELD(propertyStatus,'For Sale','For Rent','Sale Agreed'), propertyAdded DESC

This assumes you need some order though. Another good application of this would be ordering a field where its 'HIGH','MEDIUM','LOW'

posted by Daniel Mackey @ 05:49PM - Thursday 08 February 2007  

Tags: MySQL | Tips
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [13] - Leave a comment

Comments

eels said:
Your solution is very nice but if you can you should use ENUM fields. If you define a field like ENUM('b','a','c') and order by that field they would come up in that order (bac). Enums are stored internally as numbers. It's better because it won't compare the field against those values on each query run... Sorry if you already knew this (but it was not applicable to your solution), but I thought it was worth mentioning.
Oisin Prendiville said:
This seems like the perfect solution to a dilemna I'm facing but it keeps failing on me saying "'FIELD' is not a recognized function name." Any suggestions please? I've scoured google for others reporting this problem but cannot find any reference to it
Nicholas Solon said:
Wonderful! A seemingly "easy" problem became a half-hour Google search until I came upon your site. Sadly, this technique is nearly unknown to most developers. Thanks again!
joe said:
Is this for MySQL 5 or higher or will it work with version 4.1.x?
Marwin said:
Tried it in 4.0 and it works. so 5 or higher should work too...
Eshquia said:
Yes, this is works all version.
halloween costume said:
Big Thaks! Very Useful.
Shimon said:
Thank you very much - I had your solution stared in my google reader, but finally used and it helped me alot. Thank you again!

BTW - when I tried to leave comment using Opera I got message "Sorry. There was an error."
beentheredonethat said:
@Oisin Prendiville but for the benefit of anyone with the same ?:

It's due to a space left between FIELD and (. FIELD( will fix it.
i love beer said:
thanks! i had to pull a specific field *first* and was having a bit of trouble getting it to work. here's what i came up with:

order by field(FIELD,'First Result') DESC,FIELD

seems to be working as expected. :)

ps, Ireland's a great country. i look forward to visiting again.
Paul said:
I used query like this:

select username from users order by case when username='taba.ru' then 3 when username='scripted.in' then 2 when username='udobnee.net' then 1 else -1 end DESC, username ASC;

to get some records at the top of selection

Now I see function FIELD do the same but more clear to understand
Mike Johnson said:
Excellent! Works straightaway! Couldn't find this example anywhere else! Many thanks for saving me hours!
Jared - Regina Web Design said:
Very nice. Do you have any performance info on doing an order by field?