How To Sort The Result Of Multiple Queries Alternatively?

I have a query made of three select clause like this: select id, colors from table1 union all select id, numbers from table2 union all select id, names from table3 Also h

Solution 1:

This is how you can do this

select@rn:=@rn+1as id,colors from (
  (select@rn1:=@rn1+1as rn,colors from table1,(select@rn1:=0)x orderby id )
   unionall 
  (select@rn2:=@rn2+1as rn,numbers as colors from table2,(select@rn2:=0.5)x orderby id)
   unionall 
  (select@rn3:=@rn3+1as rn,names as colors from table3,(select@rn3:=0.6)x orderby id )
)x,(select@rn:=0)y orderby rn ;

The idea is to assign a rn value for each table item and need to make sure that these values are always in ascending order

So if you run the query for each table you will have

mysql>select@rn1:=@rn1+1as rn,colors from table1,(select@rn1:=0)x orderby id;
+------+--------+| rn   | colors |+------+--------+|1| red    ||2| green  ||3| blue   ||4| yellow |+------+--------+4rowsinset (0.00 sec)

mysql>select@rn2:=@rn2+1as rn,numbers as colors from table2,(select@rn2:=0.5)x orderby id;
+------+--------+| rn   | colors |+------+--------+|1.5| ten    ||2.5| two    ||3.5|one||4.5| three  ||5.5| six    ||6.5| five   |+------+--------+6rowsinset (0.00 sec)

mysql>select@rn3:=@rn3+1as rn,names as colors from table3,(select@rn3:=0.6)x orderby id;
+------+--------+| rn   | colors |+------+--------+|1.6| jack   ||2.6| peter  |+------+--------+2rowsinset (0.00 sec)

Here you can see table1 rn values are 1,2,3,....table2 values are 1.5,2.5,3.5,....table3 values are 1.6,2.6,....

so finally when you order the result with all rn it will be as

1,1.5,1.6,2,2.5,2.6,....