mysql - Sort a View by two date columns and add a rank column -

i have mysql table 1 below.

 id  name    firstdate   seconddate ==  =====   =========   ========== a1  carol   2000-07-24  1956-07-24 a2  victor  2000-07-24  1980-01-13 a3  paul    1999-12-10  1985-01-10 a4  mia     2000-06-17  1945-10-22 a5  luke    2000-07-24  1960-03-19 

i need create view following format:

1. sorted on ascending order firstdate column.

2. if 2 or more records have same value on firstdate column records use seconddate column decide witch record placed first, second , on.

3. a column rank added after seconddate column consecutive number.

like this

 id  name    firstdate   seconddate   rank ==  =====   =========   ==========   ==== a3  paul    1999-12-10  1985-01-10   1 a4  mia     2000-06-17  1945-10-22   2 a1  carol   2000-07-24  1956-07-24   3 a5  luke    2000-07-24  1960-03-19   4 a2  victor  2000-07-24  1980-01-13   5 

as

try this

    set @rank := 0;     select * , @rank := @rank + 1 rank     (     select id , name  ,  firstdate  , seconddate     table1      order firstdate  asc, seconddate asc     ) zz 

demo here

or this

   select * , @rank := @rank + 1 rank   (   select id , name  ,  firstdate  , seconddate   table1    order firstdate  asc, seconddate asc    ) zz, (select @rank := 0) z; 

demo here