Hi!
>>>>> "Brian" == Brian Neal <aceface@stripped> writes:
Brian> Well, I figured I'd have to end up using a temporary table....and this works
> great
Brian> (I was looking for a SELECT INTO, but couldn't find it):
Note that in ANSI SQL, there is no SELECT INTO; The right syntax is
INSERT INTO ...
(What does exactly SELECT INTO actually do ?)
Brian> INSERT INTO tmp_table SELECT email_addr, os_name, SUM(block_size)
Brian> AS cnt
Brian> FROM user_stats, os_types
Brian> WHERE os_type = os_number
Brian> GROUP BY email_addr, os_type ORDER BY email_addr, cnt;
Brian> So, the temp table is created, but unfortunately, I'm not having much luck with
Brian> MAX():
Brian> SELECT email_addr, os_name, block_size
Brian> AS cnt
Brian> FROM tmp_table
Brian> WHERE MAX(cnt);
Brian> ERROR 1111: Invalid use of group function
Brian> Hmmm....I tried a couple different variations but I still can't get it to work.
Brian> If only this was Oracle. :P Anyway, the version of MySQL I'm running is
> 3.22.23b,
Brian> if that helps.
Oracle wouldn't help you in this case as the syntax is illegal.
(In Oracle you could of course use a sub select, but this is another topic)
The easyest way to fix this is to create the temporary table with
a unique key on 'email_addr' and do:
INSERT IGNORE INTO tmp_table SELECT email_addr, os_name, SUM(block_size)
AS cnt FROM user_stats, os_types WHERE os_type = os_number
GROUP BY email_addr, os_type ORDER BY cnt desc;
After this the temporary table will contain your selected result set
Regards,
Monty
