SQL Syntax with AS and ORDER BY in nested queries

Question

I have a query with the following syntax:

select x.a as a, x.b as b, x.c as c
from
  (select distinct a from foo
     order by y) as x
  left join zzz....
  left join yyy...;

I want to now put in an order by into the outer select statement.

attaching it to the end - it doesn't like the syntax, and putting it before the as is apparently valid syntax, but returns an empty result set, when omitting the order by certainly returns results.

Any theories?

Sorry about the variable names, but they're not really important - it's more about the placement of the order by that I'm concerned.

Answer 1

The Order By comes after the Where in the outer Select. If there is no "Where" then you'll place it after the last On (X=X) selector in your join.

Answer 2

Did you really mean order by y (a field not included in select) for your first subquery? This bit makes your problem harder. The field you want to order by should be included in the subquery, this way you can do the order by in the outer section.

select x.a as a, x.b as b, x.c as c
from
  (select distinct a from foo) as x
  left join zzz....
  left join yyy...
order by x.a

If you did need the order by y for some reason, could you explain your question a bit better, or include the actual query you're trying to get working.

Answer 3

you can't put it after the semicolon, has to be before it

select x.a as a, x.b as b, x.c as c
from
  (select distinct a from foo
     order by y) as x
  left join zzz....
  left join yyy...
order by <column list>;