SQL Sort by temporary column "AS"

Question

I want to sort my result by column, which I actually create temoprarly

SELECT A, B, @MID(C,2,2) as X FROM foobar ORDER BY X DESC

I know this will work in MySQL , but does not work in SQL (SQLBase).

Any suggestions?

I tried:

SELECT A, B, @MID(C,2,2) as X FROM foobar ORDER BY @MID(C,2,2) DESC

The result was the same as when using Invalid Columnname ORDER BY X

Answer 1

You can also order a column ordinal. But keep in mind that if the request is modified, the proposal ORDER BYwill need to be reviewed to make sure it is ordered in the correct column. This should work for you:

SELECT A, B, @MID(C,2,2) as X 
FROM foobar 
ORDER BY 3 DESC

Another option is to use a subquery:

SELECT A, B, X
FROM
(
    SELECT A, B, @MID(C,2,2) as X 
    FROM foobar 
) AS S
ORDER BY X DESC