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


One thought on “Sorting MySQL rows using column values

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>