Php Mysql

Title of this post sounds bit strange, but the other day someone asked me to solve a sorting issue then It took a while to answer his question.

The scenario was, suppose there’s a table called ‘employees’ with structure like this;

CREATE TABLE `employee` (
`emp_id` int(11) NOT NULL auto_increment,
`designation` varchar(15) NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And after populating the records it looks like this

And here’s the desired output; All the MANAGERs on top of the list, then all PMs, then all TLs, then SSEs… and so on. i.e the order is non-alphabetic.

Here’s the solution for this.

SELECT
`emp_id`,
`designation`,
CASE `designation`
WHEN “MANAGER” THEN 1
WHEN “PM” THEN 2
WHEN “TL” THEN 3
WHEN “SSE” THEN 4
WHEN “SE” THEN 5
WHEN “DESIGNER” THEN 6
END AS level
FROM
`employee`
ORDER BY
level

And to change order of levels, we just need to change the designation corresponding numeric values in CASE.

NOTE: Please replace ” with single quotes in the above query to run.

Enjoy!

This was my thought, I’ll appreciate your thoughts, comments & suggestions.