sqlguptateamdevelopersqlbasecentura

SQL Sort by temporary "AS" column

I want to sort my result by a column that I actually create temoprarly

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

I know this would work in MySQL but it doesn't seams to 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

Result was Invalid Columnname same as using ORDER BY X

Solution

  • You can order by the column ordinal as well. But keep in mind, if the query is modified, the ORDER BY clause would need to be reviewed to be sure it's ordering on 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 would be to use a sub-query:

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