What RDBM are you using? Wouldn't this do it? -- MySQL SELECT * FROM table WHERE data1 = '5' ORDER BY DESC LIMIT 1
-- SQLServer SELECT Top 1 * FROM table WHERE data1 = '5' ORDER BY DESC ----- Original Message ----- From: "James Smith" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, July 11, 2005 8:09 AM Subject: Query Problem. >I have a problem I am sure is simple to solve but it has proved to be >beyond > me. > > Imagine I have the following data... > > StockID | Data1 | Data2 > ----------------------- > 1 | 1 | 2 > 2 | 3 | 4 > 3 | 5 | 6 > > And I want a query to return.... > > StockID | Data1 | Data2 > ----------------------- > 3 | 5 | 6 > > I am trying to use > > 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! > > I have tried using a sub-query but it just crashes the server (trying to > do > "WHERE StockID IN (over 7000 results from sub-query)". > > I am using MySQL and the actual query in question is below for your ref... > > 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 > > -- > Jay > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211531 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
