inner join and union all using order by

Question

I have this query:

SELECT B.IMAGE_ID as image_id_fav,I.Image_Path as image_path_fav 
FROM Buddies B
INNER JOIN @favDT F ON F.favorite_id = B.Reg_ID and b.favorite_id=@regID 
INNER JOIN Images I ON I.Image_ID = B.Image_ID
where b.Image_ID >0 
union all
select I.image_id as image_id_fav,I.Image_Path as image_path_fav FROM Buddies B
inner join @favDT F ON F.favorite_id = B.Reg_ID and b.favorite_id=@regID
inner join registration  R on R.Reg_ID = F.favorite_id
INNER JOIN Images I ON R.Default_Image = I.Image_ID
WHERE B.Image_ID=0
union all
SELECT I.IMAGE_ID as image_id_fav, I.IMAGE_PATH as image_path_fav FROM @favDT F
 LEFT OUTER JOIN Buddies B ON B.Reg_ID = F.favorite_id and b.favorite_id=@regID
INNER JOIN registration  R on R.Reg_ID =F.favorite_id
     INNER JOIN Images I ON R.Default_Image = I.Image_ID
     WHERE B.Reg_ID IS NULL

i need to order by the F.favorite_id but i keep getting incorrect syntax near the keyword union all

Answer 1

When you use UNION, you can't order the individual queries and expect it to return in the order you want. But you can add an order by at the end. In your case, you are trying to ORDER BY a column that you are not selecting.

You can simply add favorite_id to each select, then add an outer query that just returns your two desired columns but order by favorite_id:

SELECT image_id_fav,image_path_fav
FROM (
select B.IMAGE_ID as image_id_fav, I.Image_Path as image_path_fav, 
       F.favorite_id as FavID
from Buddies B
inner join @favDT F on F.favorite_id = B.Reg_ID and b.favorite_id = @regID
inner join Images I on I.Image_ID = B.Image_ID
where b.Image_ID > 0
union all
select I.image_id as image_id_fav, I.Image_Path as image_path_fav, 
       F.favorite_id
from Buddies B
inner join @favDT F on F.favorite_id = B.Reg_ID and b.favorite_id = @regID
inner join registration R on R.Reg_ID = F.favorite_id
inner join Images I on R.Default_Image = I.Image_ID
where B.Image_ID = 0
union all
select I.IMAGE_ID as image_id_fav, I.IMAGE_PATH as image_path_fav, 
       F.favorite_id
from @favDT F
left outer join Buddies B on B.Reg_ID = F.favorite_id and b.favorite_id = @regID
inner join registration R on R.Reg_ID = F.favorite_id
inner join Images I on R.Default_Image = I.Image_ID
where B.Reg_ID is null
)
ORDER BY FavID