user6402722 user6402722 - 4 months ago 13
SQL Question

Order Union Queries

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

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 m.* (
    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"))

    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"))

    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"))
) m
ORDER BY m.rank, m.ChildsName, m.TotalPoints;
Comments