title: How do I get the ID of a rows which have MAX and MIN values in SQL
body: <p>I am trying to make the queries my website uses more efficient.</p>
  
  <p>Being a bit vague about SQL, I've not really learnt how to use nested queries, but I have just managed to get something that is pretty near what I want.</p>
  
  <p>I sell guitars, I have a big database with all the products with different finish options listed individually. Items have unique IDs in the dB but are grouped by their title, for example, a Gibson Les Paul Standard is listed in my dB 7 times with 7 different finish options. Not all the finish options will necessarily have the same price, and not all finish options will necessarily be in stock.</p>
  
  <p>In the search results page of my website I want to be able to show:</p>
  
  <p>1) Just one record per product, ie 1 record for Gibson LP Std, which can then be sub-linked to the different finishes.</p>
  
  <p>2) The actual product displayed must <em>either</em> be the cheapest finish option, OR, the cheapest in stock.</p>
  
  <p>This is currently working on my website, but it's using N+1 queries and seems to be running dreadfully slowly, but for an example of what I mean, click here: <a href="http://www.hartnollguitars.co.uk/search.asp?subcat=Gibson-Les-Pauls" rel="nofollow">http://www.hartnollguitars.co.uk/search.asp?subcat=Gibson-Les-Pauls</a> (if the bloody thing works)</p>
  
  <p>Part one is fine, I can just group the title in SQL, it's getting part 2 out that's the problem.</p>
  
  <p>Using the following SQL query I can get the lowest price and the highest price and I have counted how many variants there are, I also have the max and min stock levels.</p>
  
  <pre><code>results.Open "SELECT * FROM 
  (SELECT *, count(id) as Variants, MAX(price) as highestPrice, MIN(price) as
  lowestPrice, MAX(shopstock) as highestStock, MIN(shopstock) as lowestStock FROM 
  products WHERE item LIKE '%"&amp; replace([searchterm]," ","%") &amp;"%' GROUP BY item) 
  AS UnknownVar LIMIT 40", conn, 3, &amp;H0001
  </code></pre>
  
  <p>What I need to be able to do is get the ID value for the rows representing the max and min stock and price values.</p>
  
  <p>I basically need to be able to run if/or logic on it and I am not sure if this is possible.</p>
  
  <p>So, I need to be able to say </p>
  
  <pre><code>if Item_With_Cheapest_Price is in stock, display this as the thumbnail &amp; link
  else 
  display first item in price sorted list where stock &gt;=1
  </code></pre>
  
  <p>I also need a fall back, if none of the finishes are in stock, display the cheapest one.</p>
  
  <p>The database is MySQL using ODBC connections, I am currently scripting in Classic ASP but aim to upgrade to .NET, once I've worked out how!!! :-)</p>