title: Order by conditioned to a field
body: <p>I would like to change the order by clause in function of a field, but I don't know whether it's possible or not.</p>
  
  <p>Here is an example:</p>
  
  <pre><code>**TABLE DISCOUNTS**
  category    offer
  A           0
  B           0
  C           1
  D           1
  F           0
  
  product_id  category    price
         1      A         100
         2      B         300
         3      C         250
         4      C         150
         5      D         200
         6      B         200
         7      F         250
  </code></pre>
  
  <p>I'm willing to select all the products but already ordered, firstly if they are an offer or not (I want to show offers first), and after that I want to group them by category and sorted by price.</p>
  
  <p>The problem is with the offers, here is not important to group by category because won't be too many offers and makes more sense to directly show them sorted by price.</p>
  
  <p>So here is the ouput I'm getting:</p>
  
  <pre><code>product_id  category    price       offer
  4            C          150         1
  3            C          250         1
  5            D          200         1
  1            A          100         0
  6            B          200         0
  7            F          250         0
  2            B          300         0
  </code></pre>
  
  <p>While I would like to get a different order (sorting offers only by price):</p>
  
  <pre><code>product_id  category    price       offer
  4            C          150         1
  5            D          200         1
  3            C          250         1
  1            A          100         0
  6            B          200         0
  2            B          300         0
  7            F          250         0
  </code></pre>
  
  <p>The query I'm using is:</p>
  
  <pre><code>select * from Products p
  left join Discounts d on p.category = d.category
  order by offer desc, p.category, price 
  </code></pre>
  
  <p>and here is the <a href="http://www.sqlfiddle.com/#!9/41dee/1" rel="nofollow">fiddle</a></p>
  
  <p>What would be the best option here?
  Thanks.</p>