Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Print Male and Female Alternate Puzzle ]  – In this puzzle we have print male and female alternate from the sample input table. Please check out the sample input and expected output for details.

Sample Input

ID NAME GENDER
1 Neeraj M
2 Mayank M
3 Pawan M
4 Gopal M
5 Sandeep M
6 Isha F
7 Sugandha F
8 kritika F

Expected Output

ID NAME GENDER
1 Neeraj M
6 Isha F
2 Mayank M
7 Sugandha F
3 Pawan M
8 kritika F
4 Gopal M
5 Sandeep M

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

 –Print alternate male and female puzzle–Create table


CREATE TABLE dbo.AlternateMaleFemale
(
ID INT
,NAME VARCHAR(10)
,GENDER VARCHAR(1)
)
GO

--Insert data
INSERT INTO dbo.AlternateMaleFemale(ID,NAME,GENDER)
VALUES
(1,'Neeraj','M'),
(2,'Mayank','M'),
(3,'Pawan','M'),
(4,'Gopal','M'),
(5,'Sandeep','M'),
(6,'Isha','F'),
(7,'Sugandha','F'),
(8,'kritika','F')

--Verify Data
SELECT ID,NAME,GENDER FROM AlternateMaleFemale

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

;WITH CTE AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY ID) rnk FROM AlternateMaleFemale
)
SELECT * FROM CTE ORDER BY rnk,ID


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com