How to get ORDER BY working within a nested select statement

Question

Can't figure out why ORDER BY within a nested select statement doesn't work... For example:

A normal select order by statement works fine:

select id from posts where zid=5 order by id desc;

but a nested select order by statement does not work?:

select id from posts where zid in (select id from zids where qid=57 order by id desc);

Is it not allowed? It doesn't produce any errors, but changing from ASC & DESC changes nothing...

Does it matter if it doesn't? Why not move the ORDER BY to the outer SELECT? — Nick, Jul 16 '17 at 21:10
yeah it matters if it doesn't. If i move the order by to the outer select, i get a different order where posts are showing according to when they were created rather than the order required. — Jonathan Laliberte, Jul 16 '17 at 21:12
Inner SELECTs don't determine the order of outer SELECTs, as you have discovered, put additional fields in your outer ORDER BY to get the correct order. — Nick, Jul 16 '17 at 21:15

Rahul · Accepted Answer · 2017-07-16T21:15:39.643

Is it not allowed?

NO it's not and moreover it doesn't make sense ordering the nested result set at all. You should be having the order by in outer query like

select id from posts 
where zid in (select id from zids where qid=57)
order by id desc;

Again, you can consider replacing your existing query with a JOIN query like

select id from posts 
JOIN zids ON posts.zid = zids.id
where zids.qid=57
order by zids.id desc;

1 Answers1