Sorting MySQL rows using column values : CodeDiesel

Sorting MySQL rows using column values

Posted in: mysql | Save to del.icio.us | Twit This! 25 Jul 2008

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

code-diesel

Sorting MySQL rows using column values

Share this post

Other related posts

Comment Form

About this blog

Recent Comments

Categories

Users Online