title: PostgreSQL Order By SubQuery
body: <p>I have this database structure:</p>
  
  <h2>Tables:</h2>
  
  <p><strong>Category</strong></p>
  
  <pre><code>id    | category    
  1     | fruit      
  2     | cars       
  3     | tables        
  </code></pre>
  
  <p><strong>Product</strong></p>
  
  <pre><code>id    | product     | category_id
  1     | banana      | 1
  2     | apple       | 1
  3     | orange      | 1
  4     | example 1   | 2
  5     | example 2   | 3
  </code></pre>
  
  <p><strong>User_List</strong></p>
  
  <pre><code>id    | product_id  | user_id | bought_date
  1     | 1           | 1       | 2012-06-21 11:00:00
  2     | 2           | 1       | 2012-06-21 06:00:00
  3     | 4           | 1       | 2012-06-21 08:00:00
  4     | 5           | 1       | 2012-06-21 01:00:00
  </code></pre>
  
  <p>what i want is create a query that "order by bought_date (desc) by category".
  In that case the expected result is:</p>
  
  <pre><code>banana
  apple
  example 1
  example 2
  </code></pre>
  
  <p>My query:</p>
  
  <pre><code>SELECT c.id, u.bought_date
      FROM categry as c
      left join product p on (c.id=p.category_id)
      left join user_list u on (p.id=u.product_id)
      WHERE u.user_id=3 
      ORDER BY u.bought_date DESC NULLS LAST
  </code></pre>
  
  <p>But this only does a simple sort by bought date...</p>
  
  <p>with this result:</p>
  
  <pre><code>banana
  example 1
  apple
  example 2
  </code></pre>