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 |
1 Response
1
Akmal Adnan
May 3rd, 2010 at 1:01 am
hye. thanks a lot. you are my lifesaver =P