Sorting MySQL rows using column values


In MySQL it is easy to sort rows by using the ORDER BY clause on the column name. Recently on a client request the sort had to be done using the column values rather than the name. Since I had never come across sorting using values, a couple of minutes of searching was in order; You can do the ordering by using the ‘field’ function like in the below example.

The first and second parameters for the field function are the column name and value respectively. If you want more than 1 value to sort by you need to add the remaining afer the second parameter like this.

...field( column_name, value1, value2, value3...)

For example:

...ORDER BY field(status, 'For Lease', 'Rent', 'Purchase') desc

Example sorting using the column names.

SELECT *
FROM property_master pm,prop_details_master pdm
WHERE pdm.property_id = pm.property_id
AND pm.active = 1
ORDER BY status desc

Example sorting using column values.

select *
FROM property_master pm,prop_details_master pdm
WHERE pdm.property_id = pm.property_id
AND pm.active = 1
ORDER BY field(status, 'For Lease') desc

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

1 Response

1

Akmal Adnan

May 3rd, 2010 at 1:01 am

hye. thanks a lot. you are my lifesaver =P

Your thoughts