Hi there, I'm back with yet some questions about group by/order by in mysql..
My first question is how group by is beeing done if I have have columns in the select query that is not in the "group by" statement. Dring many test with 3.23-42 (Myisam tables) I found out that the content of the columns that is not in the was showing values for the first physical line in the database (withing the group criteria ofcourse) and here is my question... CAN I relly on the result that I have detected, if I can, is this table type dependent? will it change if I move from myIsam to innodb or some other table type? might this change to never versions of mysql? is this different depending on the operation system that mysql is running on? I have found out that by using this fact I can get some of my queries to work even better (I do not have to do that much logic programming in the program that does the query...) My second question is regarding temporary tables and order by , the answer to this affectsmy first question :-) if I make a query in mysql like this.. create temporary table test select * from real_table order by first_key, second_key; Will the rows be stored (physically) in the order that is stated in the order by statement? when the resulting temporary table is very small (lets say less then 100 rows) is it possiblefor mysql to cache the table completely in memory and when it is larger to savce it to disk(these temporary tables will only exist a very short time (create temp......, select from temp......., drop temp.....) I really do not need them to be stored on the disc, unless they are to big and the result cannot be kept in memory.. Im asking this since I have a small problem that I have managed to solve with the above asked questions... I create a temporary table with the result from a join between 2 tables (head,lines) but since the resultcontaines to many lines (I only need 1 of them) and since I cannot use simple where to just select the line (trust me I cannot I have tried, perheps with) I use group by on the resulting temporary table. and finally drops the table... what I get is that I can "select" from which rows the data NOT in the group by statement will used as the resulting data.. one Oould say that I have by this created order by zzz group by yyy which in mysql does not exist (or in ansi sql either I guess) my other option would have been to do the join as usual, but not put the result in a temporary table, but retrieve it myself.. I would then programaticly have to create my "group by" and select the rows I want to..' with 3.23-42 on windows 2000 this actually works.. and it works very well One question to the mysql team (or any other mysql developer) since you allow columns in the query that does not exist in the group by, will you let us somehow decide which rows (from the ones grouped) the values will be taken from (like "order by zzz group by yyy" ) ? Well that was it for now from me :-) Merry X-mas and a Happy New Year! to all of you! /Christian --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
