Archive

Archive for the ‘MySQL’ Category

MySQL ordering on CUSTOM field

February 7th, 2009
No comments

There might be situation where you don’t need traditional sorting ordering. One might need to sort records in such a way that particular group of data will appear on the top. I was just looking for such functionality and bumped in to already built in functionality MySQL has: ORDER BY FIELD. Here is table and data dump.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 
-- Table structure for table `emp`
-- 
CREATE TABLE `emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(252) NOT NULL,
  `salary` DOUBLE NOT NULL,
  `status` ENUM('PAID','PENDING','APPROVED','REJECTED') NOT NULL,
  PRIMARY KEY  (`id`)
) ;
-- 
-- Dumping data for table `emp`
-- 
INSERT INTO `emp` VALUES (1, 'Jack Baur', 50500, 'PAID');
INSERT INTO `emp` VALUES (2, 'Chuck Norris', 25000, 'PENDING');
INSERT INTO `emp` VALUES (3, 'Master Shifu', 500, 'PAID');
INSERT INTO `emp` VALUES (4, 'Master Oogway', 5000, 'REJECTED');
INSERT INTO `emp` VALUES (5, 'Kung Fu Panda', 500, 'PAID');
INSERT INTO `emp` VALUES (6, 'Tigress', 500, 'APPROVED');
INSERT INTO `emp` VALUES (7, 'Mantis', 500, 'PENDING');

Default Ordering
Here is the default ordering , the default order will be the order in which you have added enum values. The image shows the query i used for this, its just simple order by clause.

Now we will see two more image that will demonstartes you the ORDER by cluase.
Image 1 -
default-ordering
Image 2 -
custom-ordering

The images are self explanatory enough to explain whats happening. Hope this helps.

MySQL