Insert ... Select Max() .. transactional Query optimization on an InnoDB table
Hi, Using MySQL-5.0.22/Win-XP-SP2 and storing data in InnoDB tables. Clients connect from VB6. Enclosing all transactions in START TRANSACTION ... COMMIT statements from VB6 clients. I have the following query for a table tmp2 with a column x of data-type INT. Insert into tmp2(x,y,x) Select ((Max(x))-((max(x) div 100) * 100)+1 as pid, 'text1','text2' from tmp2 where ((x div 100)=2147 having (((max(x))-((max(x) div 100) * 100)) 483647; I have the following questions: 1) Will the above INSERT statement run if I use constant values for y and z? without a GROUP BY clause? 2) How I can make the above select statement return a 0 (ZERO) value for pid in case it returns an empty resultset? Though I am also thinking about it but it'd be great if you guys could give me some hints. I want to keep it fast so I haven't used any functions, etc. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max in query
Claudio, Friday, June 14, 2002, 5:34:57 PM, you wrote: CC Is there a way to obtain max (or min) value in a query? CC Example: CC i want to know the last id in mytable table CC is there a way to do this query? CC something like CC select id from mytable where id = max; CC or something else? CC The one i wrote i tried it but it does not work. If the last id in your table is the maximum value per column: SELECT MAX(id) FROM table_name; Look at: http://www.mysql.com/doc/e/x/example-Maximum-column.html CC Thank you CC Claudio -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Max in query
Is there a way to obtain max (or min) value in a query? Example: i want to know the last id in mytable table is there a way to do this query? something like select id from mytable where id = max; or something else? The one i wrote i tried it but it does not work. Thank you Claudio - 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
RE: Max in query
[snip] Is there a way to obtain max (or min) value in a query? Example: i want to know the last id in mytable table is there a way to do this query? something like select id from mytable where id = max; or something else? [/snip] Try -- select MAX(id) from mytable; HTH! Jay sql, mysql, query captain, my captain, query rhythm is a wonderful thing - 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
