Order Union Queries

Question

So i have this sql:

SELECT TOP 3 Members.ChildsName, Members.MembershipType,  Results.TotalPoints
FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
WHERE (((Members.MembershipType)="Senior"))
ORDER BY Members.MembershipType, Results.TotalPoints
UNION
SELECT TOP 3 Members.ChildsName, Members.MembershipType, Results.TotalPoints
FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
WHERE (((Members.MembershipType)="Intermediate"))
ORDER BY Members.MembershipType, Results.TotalPoints
UNION
SELECT TOP 3 Members.ChildsName, Members.MembershipType, Results.TotalPoints
FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
WHERE (((Members.MembershipType)="Junior"))
ORDER BY Members.MembershipType, Results.TotalPoints;

Which gives me the desired outcome, except it is ordered alphabetically. is the a way to customise the order of the out, so in stead of:

ChildsName | MembershipType | TotalPoints
=========================================
Jon Snow   | Intermediate   | 48
HODOR      | Intermediate   | 67
Cersei     | Intermediate   | 789
Ned Stark  | Junior         | 5
Daenerys   | Junior         | 16
Bran Stark | Junior         | 456
Arya Stark | Senior         | 15
Rob Stark  | Senior         | 69
Tyrion     | Senior         | 6215

it will display:

ChildsName | MembershipType | TotalPoints
=========================================
Arya Stark | Senior         | 15
Rob Stark  | Senior         | 69
Tyrion     | Senior         | 6215
Jon Snow   | Intermediate   | 48
HODOR      | Intermediate   | 67
Cersei     | Intermediate   | 789
Ned Stark  | Junior         | 5
Daenerys   | Junior         | 16
Bran Stark | Junior         | 456

How can i do this with the code above?

Answer 1

I have wrapped the result in a subquery to order. and for grouping and ordering by membershipType I have set rank for each membership type. I hope this will help:

SELECT * FROM (
    SELECT TOP 3 Members.ChildsName AS ChildsName, Members.MembershipType AS MembershipType,  Results.TotalPoints AS TotalPoints, 1 AS rank
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE (((Members.MembershipType)="Senior"))
    ORDER BY Results.TotalPoints

    UNION ALL

    SELECT TOP 3 Members.ChildsName AS ChildsName, Members.MembershipType AS MembershipType,  Results.TotalPoints AS TotalPoints, 2 AS rank
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE (((Members.MembershipType)="Intermediate"))
    ORDER BY Results.TotalPoints

    UNION ALL

    SELECT TOP 3 Members.ChildsName AS ChildsName, Members.MembershipType AS MembershipType,  Results.TotalPoints AS TotalPoints, 3 AS rank
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE (((Members.MembershipType)="Junior"))
    ORDER BY Results.TotalPoints
) m
ORDER BY m.rank, m.ChildsName, m.TotalPoints;

Answer 2

Try this:

SELECT * 
FROM 
(
    SELECT TOP 3 Members.ChildsName, Members.MembershipType,  Results.TotalPoints, 0 AS MembershipTypeOrder
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE Members.MembershipType='Senior'
    ORDER BY Results.TotalPoints
    UNION
    SELECT TOP 3 Members.ChildsName, Members.MembershipType, Results.TotalPoints, 1 AS MembershipTypeOrder
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE Members.MembershipType)='Intermediate'
    ORDER BY Results.TotalPoints
    UNION
    SELECT TOP 3 Members.ChildsName, Members.MembershipType, Results.TotalPoints, 2 AS MembershipTypeOrder
    FROM Members INNER JOIN Results ON Members.[Member ID] = Results.ID
    WHERE Members.MembershipType='Junior'
    ORDER BY Results.TotalPoints
) ResultSet
ORDER BY ResultSet.MembershipTypeOrder, ResultSet.TotalPoints;

Alternatively, you can store the membership types in a separate table. For example:

MembershipTypeID | Name           | Order
=========================================
1                | Intermediate   | 1
2                | Junior         | 2
3                | Senior         | 0

Then, you can use an INNER JOIN to get the membership type name as well as the list order.