billoo billoo - 3 months ago 16
MySQL Question

Mysql Special Alternative Sorting

i want to make a special sorting for below mysql database table records, i want to order them depending in color so get first row red, second row green, third row red, fourth row green, and like that so i want them to be sorted as


red, green, red, green, red,..


how can this be achieved using mysql order?

below an mysql table for testing purpose:

CREATE TABLE `fav_color` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`color` enum('red','green') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'red',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

INSERT INTO `fav_color` (`id`, `name`, `color`) VALUES
(1, 'test', 'red'),
(2, 'testing 33', 'red'),
(3, 'test 444', 'green'),
(4, 'test 555', 'red'),
(5, 'test 6666', 'green'),
(6, 'test 7777', 'red'),
(7, 'test 8888', 'red'),
(8, 'test 9999', 'red'),
(9, 'test 1000', 'green'),
(10, 'test 11111', 'green'),
(11, 'test 122222', 'green'),
(12, 'test 13333333', 'green');


Thanks,

Answer

This is tricky. One way is to enumerate the values for each color and then aggregate by the enumeration:

select c.*
from (select c.*,
             (@rn := if(@c = color, @rn + 1,
                        if(@c := color, 1, 1)
                       )
             ) as rn
      from fav_color c cross join
           (select @c := '', @rn := 0) params
      order by c.color, c.id
     ) c
order by rn, field(color, 'red', 'green');