List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 28 1999 9:46pm
Subject:Re[3]: Help with select query
View as plain text  
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
Thread
Help with select queryBrian Neal20 Jun
  • Re[2]: Help with select queryBrian Neal20 Jun
  • Re[3]: Help with select queryBrian Neal21 Jun
    • Re[3]: Help with select queryMichael Widenius29 Jun