CODEWITHSUNDEEP
mysqlsql
billoo
billoo

Reputation: 115

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,

Upvotes: 3

Views: 50

Answers (3)

billoo
billoo

Reputation: 115

You can sort by color, get row numbers and cahnge order for odd and even rows: [Thanks mnv]

set @rownum := 0;
select * from (select @rownum := @rownum + 1 AS sort_col_fld, fav_color.* 
    from fav_color order by color, id) as tmp_table_alias
order by 
    -- Here @rownum is equal to count of records of subquery
    (1 - sort_col_fld % 2) * (@rownum + @rownum % 2 - sort_col_fld + 1) +  -- Even
    (sort_col_fld % 2) * sort_col_fld   -- Odd
    , color

Upvotes: 0

Nick
Nick

Reputation: 10143

You can sort by color, get row numbers and cahnge order for odd and even rows:

select * from (
  select @rownum := @rownum + 1 AS rank, fav_color.* 
  from fav_color cross join (select @rownum := 0) t
  order by color, id
) t
order by 
  -- Here @rownum is equal to count of records of subquery
  (1 - rank % 2) * (@rownum + @rownum % 2 - rank + 1) +  -- even
  (rank % 2) * rank, -- odd
  color

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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');

Upvotes: 1

Related Questions