15,036,891 members
See more:
Table:

Id(1,2,3,4,5)
Name('M','N','B','V','C')
Gender('M','F','M','M','F')

Result Required:

Id(1,2,3,5,4)
Gender('M','F','M','F','M')

They want one row Male gender then Female then male so on.......

What I have tried:

I thought of using concept of self join but not able to exactly figure out
Posted
Updated 25-Apr-21 8:57am
Wendelius 25-Apr-21 13:01pm

In order for us to help we need to know the structure of the tables.
mohit Gupta 2021 25-Apr-21 13:37pm

Wendelius 25-Apr-21 13:46pm

So does the table contain columns Id, Name, and Gender? How the id's would change? If I understand the description correctly, Id 4 is male in the data but in the result it's female...
mohit Gupta 2021 25-Apr-21 13:48pm

No its M only in result too
Wendelius 25-Apr-21 14:55pm

## Solution 1

One way to do this is to divide the data into two portions based on gender and then utilize ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^] function.

Consider the following example
```with data as (
select 1 as Id, 'M' as Name, 'M' AS Gender
union select 2 as Id, 'N' as Name, 'F' AS Gender
union select 3 as Id, 'B' as Name, 'M' AS Gender
union select 4 as Id, 'V' as Name, 'M' AS Gender
union select 5 as Id, 'C' as Name, 'F' AS Gender
)
select id, name,  gender, ROW_NUMBER() over (order by id)
from  data
where gender = 'M'
union
select id, name, gender, ROW_NUMBER() over (order by id) + 0.5
from  data
where gender = 'F'
order by 4;```