CODEWITHSUNDEEP
sqlms-access
user6402722
user6402722

Reputation: 85

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?

Upvotes: 3

Views: 63

Answers (3)

Thai Tran
Thai Tran

Reputation: 11

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.

Upvotes: 1

Vimlesh
Vimlesh

Reputation: 109

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;

Upvotes: 1

Dazak
Dazak

Reputation: 1033

Select * From (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) 
as myQuery order by 2 desc;

Upvotes: 0

Related Questions