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

Use order by clause at the end of query – Madhivanan Nov 26 '13 at 10:51 — Madhivanan, Nov 26 '13 at 10:51

score 2 · Accepted Answer · answered Nov 26 '13 at 11:04

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

score 1 · Answer 2 · answered Nov 26 '13 at 11:04

In Union All construction you cannot order every statement independently. You must put ORDER BY clause at the end of complete query.

Here is detailed explanation:

ORDER BY in UNION ALL

So, you must first include F.favourite_id column in your SELECT lists, and then it will be visible in last ORDER BY clause.

inner join and union all using order by

2 Answers2