title: SQl - Select best price based on quantity
body: <p>I have a table that contains prices for a particular item based upon the quantity being ordered and the type of client placing the order ...</p>
  
  <pre><code>ID    Name     Quantity     ClientType    Price/Unit ($)
  ========================================================
  1     Cheese      10        Consumer      20
  2     Cheese      20        Consumer      15
  3     Cheese      30        Consumer      12
  4     Cheese      10        Restaurant    18
  5     Cheese      20        Restaurant    13
  6     Cheese      30        Restaurant    10
  </code></pre>
  
  <p>I have having trouble with WHERE clause in the SQL to select the row where the customer gets the best price based upon the quantity that is ordered. The rule is they must at least meet the quantity in order to get the price for that pricing tier. If their order is below the minimum quantity then they get the Price for the first quantity (10 in this case) and if they order more than the largest quantity (30 in this example) they get that price.</p>
  
  <p>For example ... If a Restaurant orders 26 units of cheese the row with ID = 5 should be chosen. If a Consumer ordered 9 units of cheese then the row returned should be ID = 1. If the Consumer orders 50 units of cheese then they should get ID = 3.</p>
  
  <pre><code>declare @SelectedQuantity INT;
  
  SELECT * 
  FROM PriceGuide
  WHERE Name = 'Cheese'
    AND ClientType = 'Consumer'
    AND Quantity &lt;= @SelectedQuantity
  </code></pre>
  
  <p>What am I missing in the WHERE clause?</p>