title: Select unique record based on column value priority
body: <p>This is a continuation of my previous question <a href="https://stackoverflow.com/questions/11913588/select-records-based-on-column-priority">here</a>.</p>
  
  <p>In the following example:</p>
  
  <pre><code>id       PRODUCT ID    COLOUR
  1        1001          GREEN
  2        1002          GREEN
  3        1002          RED
  4        1003          RED
  </code></pre>
  
  <p>Given a product ID, I want to retrieve only one record - that with GREEN colour, if one exists, or the RED one otherwise.  It sounds like I need to employ DISTINCT somehow, but I don't know how to supply the priority rule.</p>
  
  <p>Pretty basic I'm sure, but my SQL skills are more than rusty..</p>
  
  <p><strong>Edit:</strong> Thank you everybody. One more question please: how can this be made to work with multiple records, ie. if the WHERE clause returns more than just one record? The LIMIT 1 would limit across the entire set, while what I'd want would be to limit just within each product.</p>
  
  <p>For example, if I had something like SELECT * FROM table WHERE productID LIKE "1%" ... how can I retrieve each unique product, but still respecting the colour priority (GREEN>RED)?</p>