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
