QUERY ordering clarification
Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY ordering clarification
Hello, Assume nothing. Use ORDER BY or re-think your approach to the problem. MarkP On Mon, 28 Mar 2005 09:43:09 -0800 (PST), Grant Giddens [EMAIL PROTECTED] wrote: Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Papadakis Head of RD Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY ordering clarification
Grant Giddens [EMAIL PROTECTED] wrote on 03/28/2005 12:43:09 PM: Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ No, without a GROUP BY (which, unless told to do otherwise, sorts its results) or an ORDER BY clause in your query there is no guarantee that queries will return rows in the same order for each execution. If you need to have results in a particular order, you must use an ORDER BY clause to guarantee that order. You don't have to sort by column names, you can sort on the results of functions too (created in the same pattern as your sample query): ORDER BY IF(sku=$sku1, 1, if(sku=$sku2, 2, if(sku=$sku3,3,4))) Before I get flamed... Yes, if the query's results were cached then you should get the same rows in the same order for the same query. However, there is no guarantee that your query will return (or cache) the rows sorted by the sequence of the terms your IN clause. In fact I doubt that you would get many results that happened (randomly) to be in the order of the values as you specify them in your IN clause. To guarantee a record sequence, you must use an ORDER BY. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: QUERY ordering clarification
No, it won't. You'll have to add an ORDER BY clause using FIELD, like so: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) ORDER BY FIELD(sku, $sku1, $sku2, $sku3, $sku4) ASC For more information, see page 330 of the MySQL Cookbook, or look for FIELD in the MySQL docs: http://dev.mysql.com/doc/mysql/en/string-functions.html Eamon Daly - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 28, 2005 11:43 AM Subject: QUERY ordering clarification Hi, If I do a query like: SELECT prodname, price FROM prod_table WHERE sku in ($sku1, $sku2, $sku3, $sku4) Will my results always be ordered in $sku1, $sku2, $sku3, $sku4 order? I can't really do a ORDER BY prodname or price here. I just want to make sure that that this type of query will always return the results in $sku1, $sku2, $sku3, $sku4 order. Thanks, Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
