This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to get the alternate ORDER BY result of Male and Female data.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 |
ID NAME GENDER ----------- ---------- ------ 1 Manish M 2 Nayak M 3 Martin M 4 Roy M 5 Hardik M 6 Manisha F 7 Kruti F 8 Maria F |
Expected Output:
|
1 2 3 4 5 6 7 8 9 10 |
ID NAME Gender ----------- ---------- ------ 1 Manish M 6 Manisha F 2 Nayak M 7 Kruti F 3 Martin M 8 Maria F 4 Roy M 5 Hardik M |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE MaleFemale ( ID INT ,NAME VARCHAR(10) ,GENDER CHAR(1) ) GO INSERT INTO MaleFemale VALUES (1,'Manish','M') ,(2,'Nayak','M') ,(3,'Martin','M') ,(4,'Roy','M') ,(5,'Hardik','M') ,(6,'Manisha','F') ,(7,'Kruti','F') ,(8,'Maria','F') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 |
;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY GENDER ORDER BY ID) rnk FROM MaleFemale ) SELECT ID ,NAME ,Gender FROM CTE ORDER BY rnk,ID |
Please try the different solutions for this puzzle and share it via comment...
