title: Selecting a record based on a series of criteria
body: <p>I would like to run a query that will allow me to chose the best record from a particular username based on certain criteria.  I have 2 columns (col01, col02) that are my criteria that I am looking at. </p>
  
  <p>•   If one record (username a in the example below) has both columns as yes, I would like that one to take precedence. </p>
  
  <p>•   If one record has col01 as a yes, that takes next 2nd rank precenence (username c in the example below)</p>
  
  <p>•   If one record has col01, and the other has col02 as yes, than col01 takes precedence(username d in the example below).</p>
  
  <p>•   If one record has col02 as yes, and the other records as no, than column two takes 3rd precedence (username g in the example below).</p>
  
  <p>•   If both records are the same, than neither should be returned as these records need to be investigated further (usernames b, e, f)</p>
  
  <p>Below is example sample and output. How it can be done using sql query?</p>
  
  <pre><code>+----------+-----+-------+-------+
  | username | id  | col01 | col02 |
  +----------+-----+-------+-------+
  | a        | 1  | yes    | yes   |
  | a        | 2  | yes    | no    |
  | b        | 3  | no     | no    |
  | b        | 4  | no     | no    |
  | c        | 5  | yes    | no    |
  | c        | 6  | no     | no    |
  | d        | 7  | yes    | no    |
  | d        | 8  | no     | yes   |
  | e        | 9  | no     | yes   |
  | e        | 10 | no     | yes   |
  | f        | 11 | yes    | yes   |
  | f        | 12 | yes    | yes   |
  | g        | 13 | no     | no    |
  | g        | 14 | no     | yes   |
  +----------+----+--------+-------+
  </code></pre>
  
  <p>output</p>
  
  <pre><code>+----------+-----+-------+------+
  | username | id  | col01 | col02|
  +----------+-----+-------+------+
  | a        | 1  | yes    | yes  |
  | c        | 5  | yes    | no   |
  | d        | 7  | yes    | no   |
  | g        | 14 | no     | yes  |
  +----------+----+--------+------+
  </code></pre>
  
  <p>Edit: I was asked to explain the conditions.  Basically the records come from the same area (username); The col01 is the most recently updated information we have, while col02 is older.  Both columns are important to us, so that is why it is better if both are yes; col01 being more recent is where the more dependable data is. Where all the records are exactly the same, we have to dig a little deeper to understand out data.  </p>