Fransiscus Setiawan | EV Charging & Azure Solution Architect | Sydney

I was having a problem this morning in using a ORDER BY in UNION Clause. The solution is to use keyword of “TOP 100 PERCENT” on the related ORDER BY SELECT query.

CREATE PROCEDURE MyAPLRank
@MemberID int
AS
      SET NOCOUNT ON;
BEGIN
      SELECT r.ResultName,r.PlayerOfTheYearID,r.MemberID,r.RankID,r.memberCode,
            r.FirstName,r.Surname,r.State,r.NumEvents,r.Points,r.AvgPoints,
                  r.MeetsRequirements,r.Valid,r.IsLeaderBoard,r.RowNum
      FROM
      (
            SELECT
                  'Player of The Year' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,1 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NULL AND s.WeekID IS NULL AND s.QuarterID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Month' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,2 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Week' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,3 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NOT NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID  AND MeetsRequirements = 1
      UNION

                SELECT TOP 100 PERCENT
                         v.venue As ResultName,l.leaderboardID as PlayerOfTheYearID,lq.MemberID,
                         lq.RankID,lq.membercode,lq.name as FirstName,lq.name as Surname,s.State,
                        lq.NumEvents,lq.Points,lq.points as AvgPoints,lq.valid as MeetsRequirements,
                        lq.Valid, '1' as IsLeaderBoard,4 as RowNum
                FROM
                        leaderboardquery lq
                INNER JOIN
                        leaderboard l
                ON
                        (
                                l.leaderboardid = lq.leaderboardid
                            AND
                                l.venueid IS NOT NULL
                            AND
                                lq.MemberID = @MemberID
                            AND
                                l.scheduleID =
                                        (
                                         SELECT    ScheduleID
                                         FROM    Schedule
                                         WHERE    GETDATE() BETWEEN FromDate AND ToDate
                                                AND MonthID IS NOT NULL
                                        )
                        )
                INNER JOIN
                        venues v
                ON
                        (v.venueID = l.venueid)
                INNER JOIN
                        states s
                ON
                        l.StateID = s.StateID
                ORDER BY
                    l.ScheduleID
                DESC
      ) r
ORDER BY
      RowNum
END