>>>>> "Nathan" == Nathan Wallwork <owen@stripped> writes:
>> Description:
Nathan> I just installed 3.23.3-alpha so I could try the TEMPORARY tables...
Nathan> I'm trying to set up a query to find each customers' most recent order,
Nathan> then I plan to show the orders that are about to expire.
Nathan> The manual entry for CREATE TABLE syntax includes
Nathan> select_statement:
Nathan> [IGNORE | REPLACE] SELECT ... (Some legal select statement)
Nathan> The problem is that I have a legal select statement I would like to use while
Nathan> creating a table, but it doesn't work when I include it as part of the
Nathan> create statement.
Nathan> If I just try the select statement, it works.
mysql> SELECT OrderID, max(OrderDate)
-> FROM Customers,Orders
-> WHERE Customers.CustomerID=Orders.CustomerID &&
-> not isnull(Orders.OrderID)
-> GROUP BY Customers.CustomerID;
>> +---------+----------------+
>> | OrderID | max(OrderDate) |
>> +---------+----------------+
>> | 1 | 1999-03-24 |
>> | 2 | 1999-08-30 |
>> | 4 | 1998-11-01 |
Nathan> [...]
Nathan> But if I try to use it in the create statement, it doesn't work.
mysql> CREATE TEMPORARY TABLE MostRecentOrder
-> (OrderID int not null, OrderDate date, PRIMARY KEY(OrderID))
-> SELECT OrderID, max(OrderDate)
-> FROM Customers,Orders
-> WHERE Customers.CustomerID=Orders.CustomerID &&
-> not isnull(Orders.OrderID)
-> GROUP BY Customers.CustomerID;
>> ERROR 1166: Incorrect column name 'max(OrderDate)'
>> How-To-Repeat:
Nathan> use test;
Nathan> create table example (ID int not null, A int, primary key(ID));
Nathan> select ID, max(A) from example group by ID;
Nathan> create table temp select ID, max(A) from example group by ID;
Hi!
The problem is that 'max(A)' is not a legal column name.
Fix:
Give the column an alias:
create table temp select ID, max(A) as max_a from example group by ID;
Regards,
Monty
