Why can't "SELECT DISTINCT 1 AS score ORDER BY score DESC;" work when used with creation of temporary table.

Question

Hello... In MySQL, this portion of code works and produces a single row of '1' as value.

SELECT DISTINCT 1 AS score
ORDER BY score DESC;

However, when I use it with creating temporary tables as in the one below:

DROP TEMPORARY TABLE IF EXISTS `testTable`;
CREATE TEMPORARY TABLE `testTable`
SELECT DISTINCT 1 AS score
ORDER BY score DESC;

MySQL would say: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by score desc' at line 3

Note that the code below works... the one without ORDER BY

DROP TEMPORARY TABLE IF EXISTS `testTable`;
CREATE TEMPORARY TABLE `testTable`
SELECT DISTINCT 1 AS score;

Appending it with a FROM clause as shown below, would work...

DROP TEMPORARY TABLE IF EXISTS `testTable`;
CREATE TEMPORARY TABLE `testTable`
SELECT DISTINCT 1 AS score
FROM tableX
ORDER BY score DESC;

I know that my sample above is not significant, but this is more of a curiosity question. Thanks.

Answer 1

The select-statement is perfectly valid so it ought to work, but perhaps MySQL is a bit picky or buggy about this statement.

You are probably missing the datatype of the column where data is to be stored. Adding a column name along with datatype makes it work.

CREATE TEMPORARY TABLE testTable (score INTEGER) SELECT 1 AS score;

If not specifying datatype the create statement in this case has to guess if it is a tinyint, smallint, integer... If you do the select from a table the datatype is defined in the table.

Answer 2

Should it be CREATE TEMPORARY TABLE testTable AS SELECT ...? (That is, add an AS after the table name.)