Show alternate result based on column value

Question

I have a table with the data below

id      name            gender(Enum : Male,Female)
1       John Doe        Male
2       Panther Jow     Male
3       Faran Aktar     Female
4       Rohan Fusi      Male
5       Soham Das       Female
6       Rohit Dutta     Female
7       Sozi Petacus    Male
8       Tuhin Dtui      Male

I need a sql query in which the result should be displayed using one man and one woman, and then again one man and one woman continuously. if after a male or female result, if it is not possible to obtain the following female or male data, then the entire result will be shown. like this

   id      name            gender(Enum : Male,Female)
    1       John Doe        Male
    3       Faran Aktar     Female
    2       Panther Jow     Male
    5       Soham Das       Female
    4       Rohan Fusi      Male
    6       Rohit Dutta     Female
    7       Sozi Petacus    Male 
    8       Tuhin Dtui      Male

If someone can help me, I will be happy for him / her. thanks in advance

Answer 1

You can try the following:

SELECT rnk, name ,gender
FROM (
  SELECT STRAIGHT_JOIN id, name, @y:=IF(@gender=gender,@y+1,1) AS rnk, @gender:=gender AS gender
  FROM (SELECT @gender:=NULL) AS x
  JOIN test
  ORDER BY gender, name DESC
) AS t
ORDER BY rnk ASC, gender DESC;

FIDDLE DEMO

Answer 2

Hi try this -

    SELECT * 
    FROM <table_name> 
    WHERE IF( gender =  "Male",  "Female",  "Male");