CODEWITHSUNDEEP
mysqlsql
2dvisio
2dvisio

Reputation: 911

Interleave rows of MySQL table

I have a table containing data and a class, something like

----------------
| DATA | Class |
----------------
|  1   |   A   |
|  2   |   A   |
|  5   |   B   |
|  10  |   A   |
|  2   |   A   |
|  45  |   B   |
|  90  |   B   |
----------------

I would like to interleave the two classes to obtain something like this:

----------------
| DATA | Class |
----------------
|  1   |   A   |
|  5   |   B   |
|  2   |   A   |
|  45  |   B   |
|  2   |   A   |
|  90  |   B   |
|  10  |   A   |
----------------

I thought about generating on the fly an additional column that will add a sequential incrementing index to each separate class and sort on that one, but need a good direction/pointer to do that.

Something like:

-----------------------
| DATA | Class | indx |
-----------------------
|  1   |   A   |  1   |
|  5   |   B   |  1   |
|  2   |   A   |  2   |
|  45  |   B   |  2   |
|  2   |   A   |  3   |
|  90  |   B   |  3   |
|  10  |   A   |  4   |
-----------------------

Is it even possible using only MySQL?

Upvotes: 2

Views: 1398

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270533

Enumerate each class independently, and then order by the enumeration:

select data, class, idx
from (select a.*,
             (@seqnum := if(@class = class, @seqnum + 1,
                            if(@class := class, 1, 1)
                           )
             ) as idx
      from atable a cross join
           (select @class := NULL, @seqnum := 0) vars
      order by class, data
     ) a
order by idx, class;

EDIT:

This query enumerates each class independently, so the row with the smallest data gets a value of 1 and so on. It is using MySQL variables for this purpose. In other databases, you would use row_number(). Once you have the enumeration, the final step is just to sort by that field.

Upvotes: 4

Stepashka
Stepashka

Reputation: 2698

Here is the SQL I can suggest:

SET @rank:=0;
update aabb set `index` = @rank:=@rank+2 where `class` = 'A';
SET @rank:=1;
update aabb set `index` = @rank:=@rank+2 where `class` = 'B';
select * from aabb order by `index`;

Upvotes: 0

Related Questions