James Smith wrote: > > SELECT max(stockid) as stockid, data1, data2 > FROM table > HAVING data1 = 5 > > Unfortunately this is returning the following data.... > > StockID | Data1 | Data2 > ----------------------- > 3 | 5 | 2 > > Ie: the correct stockid and data1 but data2 from a diferent row!
The MySQL manual warns against that: http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html They also document how it should be done: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html > I have tried using a sub-query but it just crashes the server Did you file a bug? > (trying to do > "WHERE StockID IN (over 7000 results from sub-query)". > SELECT s.ItemID, s.ASIN, MAX(q.StockID) AS StockID, q.Condition, > q.MaximumSalePrice, q.MinimumSalePrice, q.AddedToFile, q.CurrentAmazonPrice > FROM stockitemdetails s > JOIN stockquantities q ON (s.ItemID = q.ItemID) > WHERE q.Quantity > 0 > AND s.ASIN != "" > AND s.ASIN IS NOT NULL > AND s.ASIN != "N/A" > AND LEFT(s.ASIN,1) != "<" > GROUP BY s.ASIN > HAVING AddedToFile = 0 > ORDER BY s.ItemID Could you show us the query that would give the right results, but crashes your server? The result of this query is undetermined (which is why every database except MySQL will throw an error, MySQL will happily return the wrong answer) so it is a bit hard to suggest alternatives. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211545 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
