Not able to use order by in Union query-CodePudding

I have the below original mysql query

select *, r.ReviewId AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r LEFT join reviewphoto rp ON r.ReviewId = rp.ReviewId where r.DishId = ?
      UNION
      select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r RIGHT join reviewphoto rp ON r.ReviewId = rp.ReviewId where r.DishId = ?

I am trying to add order by so that i can sort the data by date but it is giving me below error

#1250 - Table 'rp' from one of the SELECTs cannot be used in ORDER clause

Here is the modified query below:

(select *, r.ReviewId AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r LEFT join reviewphoto rp ON r.ReviewId = rp.ReviewId where r.DishId = 46)
      UNION
      (select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r RIGHT join reviewphoto rp ON r.ReviewId = rp.ReviewId where r.DishId = 46) order by rp.DateCreated desc

CodePudding user response：

Your last query should work if you remove the alias from the ORDER BY clause:

(SELECT *, r.ReviewId AS ActualReviewId, r.DateCreated AS ActualDateCreated
 FROM review r
 LEFT JOIN reviewphoto rp ON r.ReviewId = rp.ReviewId
 WHERE r.DishId = 46)
 UNION
(SELECT *, r.ReviewId, r.DateCreated
 FROM review r
 RIGHT JOIN reviewphoto rp ON r.ReviewId = rp.ReviewId
 WHERE r.DishId = 46)
ORDER BY ActualDateCreated DESC;