How to get 2 result output tables from the same input table in one query, while maintaining matched pairs of results

In my previous question, I got a results table of the form:

male_year	age	male_Gender	male_OrderID	female_year	female_Gender	female_OrderID
2012	18	M	4268	2013	F	4269
2021	75	M	4269	2020	F	4270
2018	29	M	4271	2019	F	4272
2016	56	M	4273	2015	F	4270
2014	33	M	4274	2014	F	4272
2022	40	M	4001	2017	F	4002

The code to give this result is:

WITH male_sample AS (
    SELECT TOP (200) [Year],[Gender],[Age],[OrderID]
    FROM [test]
    WHERE AgeYrend <= 90 AND Sex = 'M'
    ORDER BY NEWID()),
female_sample AS (
    SELECT TOP (200) m.[Year] AS male_Year, m.[Age] AS age, m.[Gender] AS male_Gender, m.[OrderID] AS male_OrderID,
           f.[Year] AS female_Year, f.[Gender] AS female_Gender, f.[OrderID] AS female_OrderID
    FROM male_sample m
    INNER JOIN [Test] f ON m.Age = f.Age
    WHERE f.Sex = 'F'
ORDER BY NEWID())

SELECT *
FROM female_sample
ORDER BY Age;

I then tried to modify this to get 2 separate results tables - one for male and one for female - while still maintaining the pairs of results of a male and female of the same age. This is the code I have tried:

WITH male_sample AS (
    SELECT TOP (200) [Year],[Gender],[Age],[OrderID]
    FROM [test]
    WHERE AgeYrend <= 90 AND Sex = 'M'
    ORDER BY NEWID()),
female_sample AS (
    SELECT TOP (200) m.[Year] AS male_Year, m.[Age] AS age, m.[Gender] AS male_Gender, m.[OrderID] AS male_OrderID,
           f.[Year] AS female_Year, f.[Gender] AS female_Gender, f.[OrderID] AS female_OrderID
    FROM male_sample m
    INNER JOIN [Test] f ON m.Age = f.Age
    WHERE f.Sex = 'F'
ORDER BY NEWID())

SELECT male_Year, Age, male_Gender, male_OrderID
FROM female_sample
ORDER BY Age

SELECT female_Year, Age, female_Gender, female_OrderID
FROM female_sample
ORDER BY Age;

SQL Server does not like the final 3 lines of code giving the following error -

Msg 208, Level 16, State 1, Line 18
Invalid object name 'female_sample'.

I have tried fiddling with the code in various ways but nothing I have tried works, can anyone point out where I am going wrong please?

Solution

As @Dale suggested, you can create a temp table and select male and female with a filter of Gender.

I extended the previous fiddle here.

Temp table approach

1.Create temp table

CREATE TABLE temp (
    Year INT,Age INT, Gender CHAR(1),
    OrderID INT
);

2.Insert the result set of the query in the temp table

-- < your union query>
INSERT INTO temp (Year, Age, Gender, OrderID)
SELECT [Year], [Age], [Gender], [OrderID]
FROM combined_sample
ORDER BY Age, Gender;

3.Select Male samples from the temp table.

 select * from temp where gender = 'M';

Male sample data

Year	Age	Gender	OrderID
2012	18	M	4268
2018	29	M	4271
2014	33	M	4274
2022	40	M	4001
2023	50	M	5001
2016	56	M	4273
2024	60	M	6001
2021	75	M	4269

4.Select Female samples from the temp table.

select * from temp where gender = 'F';

Female sample data

Year	Age	Gender	OrderID
2013	18	F	4269
2019	29	F	4272
2014	33	F	4272
2017	40	F	4002
2011	50	F	5002
2015	56	F	4270
2010	60	F	6002
2020	75	F	4270

If you dont want to create temp table, you can split the query like so

Male samples

WITH male_sample AS (
    SELECT TOP (200) [Year], [Age], [Gender], [OrderID]
    FROM test
    WHERE Age <= 90 AND Gender = 'M'
)

select * from male_sample order by AGE ;

Generates

Year	Age	Gender	OrderID
2012	18	M	4268
2018	29	M	4271
2014	33	M	4274
2022	40	M	4001
2023	50	M	5001
2016	56	M	4273
2024	60	M	6001
2021	75	M	4269

Female Sample

WITH male_sample AS (
    SELECT TOP (200) [Year], [Age], [Gender], [OrderID]
    FROM test
    WHERE Age <= 90 AND Gender = 'M'
),
female_sample AS (
    SELECT TOP (200) m.[Year] AS male_year, m.[Age] AS age, m.[Gender] AS male_gender, m.[OrderID] AS male_OrderID,
           f.[Year] AS female_year, f.[Gender] AS female_gender, f.[Age] AS female_age, f.[OrderID] AS female_OrderID
    FROM male_sample m
    INNER JOIN test f ON m.Age = f.Age
    WHERE f.Gender = 'F'
)

select female_year,female_gender,female_age ,female_OrderID
  from female_sample 
  order by female_age;

Generates

female_year	female_gender	female_age	female_OrderID
2013	F	18	4269
2019	F	29	4272
2014	F	33	4272
2017	F	40	4002
2011	F	50	5002
2015	F	56	4270
2010	F	60	6002
2020	F	75	4270