Union of tables in MySql

Question

I am performing union on two select statements but the result I am getting contains the random rows like some from table one and some from table two.But I want to get first all the rows from table one and then from table two.I am using MySql database.

  Table 1
==========================
s_no     Name        Marks
1       nikhil         25

  Table 2
====================
s_no  Name     Marks
1     Akhil     10  
2     Mark      20
1     Kim       40

Here is the query that I am using:

select * from (
      select t2.s_no,t2.name,t2.marks from table1 t2
  union all
      select t1.roll_no,t1.name,t1.marks from table1 t1
      ) a order by s_no desc

Here are the results:

  Actual result
=========================
s_no     Name     Marks
1        Akhil       10
1        nikhil  25  <<<
1        kim     40
2        mark        20

  required result
====================
s_no    Name     marks
1       Akhil      10
1       Kim        40
1       nikhil     25  <<<
2       mark       20

Answer 1

Try this. You need to add order by s_no,name in the last select query

(select * from table1)
union
(select * from table2) order by s_no,name