List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 7 1999 4:16pm
Subject:CREATE TABLE ... SELECT max(foo) ... GROUP BY bar;
View as plain text  
>>>>> "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
Thread
CREATE TABLE ... SELECT max(foo) ... GROUP BY bar;Nathan Wallwork3 Sep
  • CREATE TABLE ... SELECT max(foo) ... GROUP BY bar;Michael Widenius8 Sep