CODEWITHSUNDEEP
mysqlsql
Pinal Patel
Pinal Patel

Reputation: 157

How do I order results by alternating (0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0,...) rows

I'm using MySQL to perform database transactions and PHP to run MySQL queries. I want to fetch the query result by this sequence using value of is_free column (0, 0, 0, 1, 1, 1, 1, 1, 1, 0, 0, 0,...). I want to fetch records by a ratio like 3:6 for the value of is_free column.

Is there any possible way to achieve this?

I've searched for different algorithms but nothing is found as per my requirement. I've found the only query which throws result by alternative records but my requirement not fulfilled.

I expect the output like

id     is_free
--     -------
1      0
2      0
3      0
4      1
5      1
6      1
7      1
8      1
9      1
10     0
11     0
12     0

Upvotes: 1

Views: 278

Answers (3)

nice_dev
nice_dev

Reputation: 17815

select id,is_free,rank
from (
         (
            select id,is_free,if(@row_num = @target,@target := @target + 9,@target := @target + 0) as target,if(@target - @row_num > 3,@row_num := @row_num + 7,@row_num := @row_num + 1) as rank
            from test,(select @row_num := 0,@target := 3) t2
            where is_free = 0
         )
        union
          (
            select id,is_free,if(@row_num2 = @target2,@target2 := @target2 + 9,@target2 := @target2 + 0) as target,if(@target2 - @row_num2 > 8,@row_num2 := @row_num2 + 4,@row_num2 := @row_num2 + 1) as rank
            from test,(select @row_num2 := 3,@target2 := 9) t3
            where is_free = 1
          )
) derived
order by rank,is_free,id;

Demo: https://www.db-fiddle.com/f/pc9oCwwtWjacETVeGCMtcw/0

  • The idea above is to give a rank to every row in such a way that we could maintain a 3:6 ratio.

  • If we closely observe, the 0's follow the series of ranks as 1 , 2 , 3 , 10 , 11 , 12 , 19 , 20 , 21 and 1's follow the series of 4, 5, 6, 7, 8, 9, 13, 14, 15, 16, 17, 18, 22, 23 ... as shown below:

Series:

0   0   0   1   1   1   1   1   1   0   0   0   1   1   1   1   1   1   0   0   0

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

  • So, in the above query, we first fetch all records of 0 and assign row numbers to them in the above fashion starting from 1. We then fetch all records of is_free = 1 in the same manner by assigning row numbers starting from 4.

  • In the end, we combine both results using union and order them by their ranks.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272296

You need something similar to ROW_NUMBER() OVER (PARTITION BY is_free ORDER BY id). Once you have the row numbers for each partition (e.g. 0-0, 0-1, 0-2, 0-3, ..., 1-0, 1-1, 1-2, 1-3, ...) you can use basic math to sort the results (values 0-0...0-2 and 1-0...1-5 go first, 0-3...0-5 and 1-6...1-11 go second and so on). Unfortunately MySQL 5.x does not support window functions so you need to calculate row numbers using some kind of trick. Here is one:

SELECT *
     , rn div IF(is_free = 1, 6, 3) AS gn
FROM (
    SELECT id
         , is_free
         , (SELECT COUNT(*) FROM t AS x WHERE x.id < t.id AND x.is_free = t.is_free) AS rn
    FROM t
) AS x
ORDER BY gn, is_free

Demo on db<>fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270683

In MySQL 8+, you can use:

order by floor( (row_number() over (partition by is_free order by id) - 1) / 4),
         is_free, id

The arithmetic on row_number() batches them. You can do something similar with variables in earlier versions:

select t.id, t.is_free
from (select t.*,
             (@rn := if(@if = is_free, @rn + 1,
                        if(@if := is_free, 1, 1)
                       )
             ) as rn
      from (select t.* from t order by is_free, id) t cross join
           (select @if := -1, @rn := 0) params
     ) t
order by floor((rn - 1) / 4), is_free, id;

Upvotes: 0

Related Questions