What's Wrong with My ORDER BY Clause???

[leishtheman]

Hello

I'm hoping somebody can educate me around this puzzle.

I've been trying to sort descending on a basic calculated field within a basic query. The ORDER BY clause refuses to work however (keeps prompting for parameter values to be entered) unless I use the field position indicator...why??? All the advice I've googled suggests I'm doing it correctly...

refuses to work:

SELECT SUPPLIER, SUM(UN.Y1) AS T_Y1, SUM(UN.Y2) AS T_Y2, (T_Y2-T_Y1) AS DIFF

FROM (

SELECT SUPPLIER, SPEND AS Y1, 0 AS Y2
FROM Table1
WHERE YR = 2015

UNION

SELECT SUPPLIER, 0 AS Y1, SPEND AS Y2
FROM Table1
WHERE YR = 2016) AS UN

GROUP BY SUPPLIER

ORDER BY
(T_Y2-T_Y1) DESC


...works fine:


SELECT SUPPLIER, SUM(UN.Y1) AS T_Y1, SUM(UN.Y2) AS T_Y2, (T_Y2-T_Y1) AS DIFF

FROM (

SELECT SUPPLIER, SPEND AS Y1, 0 AS Y2
FROM Table1
WHERE YR = 2015

UNION

SELECT SUPPLIER, 0 AS Y1, SPEND AS Y2
FROM Table1
WHERE YR = 2016) AS UN

GROUP BY SUPPLIER

ORDER BY
4 DESC


Explanation would be most appreciated.

Many thanks,
Andy

 

[Joe4]

I am pretty sure that you cannot use mathematical computations or aliases in your ORDER BY clause.
You either need to use a Field Name (if Table field and not calculated field), or the ordinal field number (being returned in the query).
I always use the number, like you did in your second example when using UNION queries.

 

[xenou]

Note that when googling for SQL you have to be sure that the results are specifically for Access, not SQL in general (there are many examples that would work in SQL Server that wouldn't work in Access). Generally, you have to accept a few Access quirks.

I don't see a query field that is the result of two other aggregate function fields in the same Select clause very often ... SELECT T_Y1, T_Y2, (T_Y2 - T_Y1) AS Diff ... so in this case example are probably rare and you probably want to just go with what works.

For order by issues in general, if you have order by problems you can always wrap everything in a virtual table and then order that:

select T.a, T.b, T.c
from 
(
.... lots of sql stuff
) as T
order by T.a, T.b, T.c

 

[leishtheman]

Thanks guys. I figured it out yesterday in the end, needed to remove references to all alias's formed in the top level query:


SELECT SUPPLIER, SUM(UN.Y1) AS Y1, SUM(UN.Y2) AS Y2, Y2-Y1 AS DIFF
FROM (SELECT SUPPLIER, SPEND AS Y1, 0 AS Y2
FROM Table1
WHERE YR = 2015

UNION

SELECT SUPPLIER, 0 AS Y1, SPEND AS Y2
FROM Table1
WHERE YR = 2016) AS UN
GROUP BY SUPPLIER
ORDER BY (SUM(UN.Y1) - SUM(UN.Y2)) DESC;


Andy