swap one female name in-between two male name

I tried to swap one female name in between two male names

Input

Id   Name  Sal   Gender
1     AA   2000   M
2     BB   1500   M
3     CC   3000   F

expected output could be

Id   Name  Sal   Gender
1     AA   2000   M
3     CC   3000   F
2     BB   1500   M

One option is to split people by gender, fetching rownum for each of them so that later sorting returns desired output.

With sample data:

SQL> select * from test;

        ID NAME         SAL GENDER
---------- ----- ---------- ----------
         1 AA          2000 M
         2 BB          1500 M
         3 CC          3000 F

Query:

SQL> with temp as(
  2    select t.*, rownum rn
  3    from test t
  4    where gender = 'M'
  5    union
  6    select t.*, rownum rn
  7    from test t
  8    where gender = 'F'
  9  )
 10  select row_number() over (order by rn) id,
 11    name, sal, gender
 12  from temp
 13  order by rn, gender;

        ID NAME         SAL GENDER
---------- ----- ---------- ----------
         1 AA          2000 M
         2 CC          3000 F
         3 BB          1500 M

SQL>

Try this:

SELECT ID, A_NAME, SAL, GENDER
FROM    ( Select 
              ID, A_NAME, SAL, GENDER, ROWNUM "RN", ROW_NUMBER() OVER(Partition By GENDER Order By ID) "GENDER_RN" 
          From tbl
          Order By ID
        )
Order By GENDER_RN, RN

... with your sample data:

WITH
    tbl AS
        ( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
          Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
          Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual 
        )

... it results with:

        ID A_NAME        SAL GENDER
---------- ------ ---------- ------
         1 AA           2000 M      
         3 CC           3000 F      
         2 BB           1500 M     

And if you have some more rows like here:

WITH
    tbl AS
        ( Select 1 ID, 'AA' "A_NAME", 2000 "SAL", 'M' "GENDER" From Dual Union All
          Select 2 ID, 'BB' "A_NAME", 1500 "SAL", 'M' "GENDER" From Dual Union All
          Select 3 ID, 'CC' "A_NAME", 3000 "SAL", 'F' "GENDER" From Dual Union All
          --
          Select 4 ID, 'DD' "A_NAME", 1800 "SAL", 'F' "GENDER" From Dual Union All
          Select 5 ID, 'EE' "A_NAME", 2100 "SAL", 'M' "GENDER" From Dual Union All
          Select 6 ID, 'FF' "A_NAME", 2700 "SAL", 'F' "GENDER" From Dual )

... the result is:

        ID A_NAME        SAL GENDER
---------- ------ ---------- ------
         1 AA           2000 M      
         3 CC           3000 F      
         2 BB           1500 M      
         4 DD           1800 F      
         5 EE           2100 M      
         6 FF           2700 F    

To reverse the order from M F M F into F M F M use DESC on RN in Order By clause...