SQL Sort by temporary "AS" column

Question

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

ok, I have to edit my post a bit to make clear whats wrong ^^ @MID actually cuts me some digits out that I need for the ORDER BY — Dwza, Nov 12 '15 at 14:42
I allready tried this... error. He searches for a non exisiting color — Dwza, Nov 12 '15 at 14:44
Give this a shot `select * from (SELECT A, B, @MID(C,2,2) as X FROM foobar) t order by X)` — zedfoxus, Nov 12 '15 at 14:47
omg, you nailed it @Siyual !!!!! Post it I will vote it up an accept it — Dwza, Nov 12 '15 at 14:49
@christiandev in sql, at least in SQLBase sql its kind of difficult using sub-querys. — Dwza, Nov 12 '15 at 14:51
Caution: If at anytime columns change in your select the 3 may need to be adjusted, it's why typically order bys are column specific, not position specific. There are always exceptions but just be aware of the risk. — xQbert, Nov 12 '15 at 14:51
@xQbert I allready noticed this. But in my case this is not necessary. I build my select with the order by relevant column on first place and select everthing afterwards... so i wont run into this problem. Also the source I code will propably never be changed again, but as long as im not sure that a meteor falls on the earth next week, ill code it like some one may change it soon :D But thx for this hint. — Dwza, Nov 12 '15 at 18:01

Siyual · Accepted Answer · 2015-11-12T14:55:20.387

4

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

edited Nov 12 '15 at 14:55

answered Nov 12 '15 at 14:51

Siyual

16,415
8
44
58

my reservation with this is that an alteration to the query, say `SELECT A, B, NewCol, @MID(C,2,2) as X`, then it will order on `NewCol` – Christian Phillips Nov 12 '15 at 14:53
@christiandev This is true - I've also added a sub-query option as well. – Siyual Nov 12 '15 at 14:54
@Siyual, the sub query is the way to go I think, I mentioned it above. – Christian Phillips Nov 12 '15 at 14:55
The Sub-Query wont work in SQLBase SQL. Actually I dont know why but it wont work :) Causes a Unknown Table Error or something like that. – Dwza Nov 12 '15 at 14:55

SQL Sort by temporary "AS" column

1 Answers1