Cannot sort by a renamed column?

Databases
1

EDIT: I meant “aliased column”, not “renamed column”.

Can someone explain to me why this doesn’t work?


SELECT Col1, Col2 AS 'RenamedCol' FROM MyTable ORDER BY RenamedCol

It refuses to obey the “ORDER BY” clause, but if I do this…


SELECT Col1, Col2 AS 'RenamedCol' FROM MyTable ORDER BY Col2;

…it sorts it as expected.

I can’t see reason why the first query shouldn’t work.

2

Yup, thanks.

3

ORDER BY ‘CAD m/u’ is ineffective because you’re sorting on a string (‘CAD m/u’)

mysql lets you use single quotes to assign a column alias, because the syntax is unambiguous – whatever comes after the AS is the alias

but it does not let you use single quotes to refer to a column alias, because there’s no way to distinguish that from a string

make sense?

4

You shouldn’t need to use backticks in any case. The only time you’d use them is if the name of the column or the alias name were reserved words and you wouldn’t use those right? :-o

5

neither was i

‘these are single quotes’

these are backticks

:slight_smile:

6

Ah… problem solved, I wasn’t using backticks.

Move along, nothing to see here, folks…

7 
mysql> SELECT ItemID, CADMarkupPercent AS 'CAD m/u' FROM Items ORDER BY `CAD m/u` LIMIT 3;
+--------+---------+
| ItemID | CAD m/u |
+--------+---------+
|     35 |    0.00 |
|     53 |   15.00 |
|     52 |   15.00 |
+--------+---------+
3 rows in set (0.00 sec)

mysql> SELECT ItemID, CADMarkupPercent AS 'CAD m/u' FROM Items ORDER BY 'CAD m/u' LIMIT 3;
+--------+---------+
| ItemID | CAD m/u |
+--------+---------+
|      8 |  100.00 |
|     11 |   50.00 |
|     12 |  100.00 |
+--------+---------+
3 rows in set (0.00 sec)

It’s not because of the wonky symbols in the alias either, I tried them without.

8

it does so work

setup –

CREATE TABLE MyTable 
( Col1 INTEGER NOT NULL 
, Col2 VARCHAR(99)
);
INSERT INTO MyTable VALUES
 (1,'one')
,(2,'two')
,(3,'buckle my shoe')
,(4,'four')
,(5,'shut de door')
; 
SELECT Col1
     , Col2 AS 'RenamedCol' 
  FROM MyTable 
ORDER 
    BY RenamedCol
;

results –


[B][COLOR="Blue"]Col1  RenamedCol[/COLOR][/B]
  3   buckle my shoe
  4   four
  1   one
  5   shut de door
  2   two