title: How do I do multiple selection based on a flowchart of criteria?
body: <p>Table name:  Copies</p>
  
  <pre><code>+------------------------------------------------------------------------------------+
  |         group_id  |  my_id   |  previous  |  in_this   |  higher_value |  most_recent  |
  +----------------------------------------------------------------------------------------------------------------
  |        900        |    1     |   null     |     Y      |       7       |       May16    |
  |        900        |    2     |   null     |     Y      |       3       |       Oct  16    |
  |        900        |    3     |   null     |     N      |       9       |       Oct 16      |
  |        901        |    4     |   378      |     Y      |      3        |      Oct 16      |
  |        901        |    5     |   null     |     N      |      2        |       Oct 16     |
  |       902         |    6     |   null     |     N      |       5       |       May16    |
  |       902         |    7     |   null     |     N      |       9       |     Oct 16    |
  |       903         |    8     |   null     |     Y      |       3       |        Oct 16    |
  |       903         |    9     |   null     |     Y      |       3       |         May16   |
  |      904          |   10     |   null     |     N      |       0       |       May 16   |
  |      904          |   11     |   null     |     N      |       0       |       May16
  --------------------------------------------------------------------------------------
  </code></pre>
  
  <p>Output table</p>
  
  <pre><code>+---------------------------------------------------------------------------------------------------+
  |    group_id        |  my_id       |  previous     |  in_this       |  higher_value               |most_recent|
  +----------------------------------------------------------------------------------------------------
  |        900        |    1         |   null         |     Y          |       7                   |       May16    |
  |       902         |    7         |   null         |     N          |       9                   |        Oct 16    |
  |       903         |    8         |   null         |     Y           |       3                   |        Oct 16    |
  ---------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>Hi all, I need help with a query that returns one record within a group based on the importance of the field.  The importance is ranked as follows:</p>
  
  <p><strong>previous</strong>- if one record within the group_id is not null, then neither record within a group_id is returned (because according to our rules, all records within a group should have the same previous value)</p>
  
  <p><strong>in_this</strong>- If one record is Y, and the other is N within a group_id, then we keep the Y; If all records are Y or all are N, then we move to the next attribute</p>
  
  <p><strong>Higher_value</strong>- If all records in the ‘in_this’ field are equal, then we need to select the record with the greater value from this field.  If both records have an equal value, we move to the next attribute</p>
  
  <p><strong>Most_recent</strong>-   If all records were of equal value in the ‘higher_value’ field, then we consider the newest record.  If these are equal, then nothing is returned.</p>
  
  <p>This is a simplified version of the table I am looking at, but I just would like to get the gist of how something like this would work.  Basically, my table has multiple copies of records that have been grouped through some algorithm.   I have been tasked with selecting which of these records within a group is the ‘good’ one, and we are basing this on these fields.</p>
  
  <p>I’d like the output to actually show all fields, because I will likely attempt to refine the query to include other fields (there are over 40 to consider), but the most important is the group_id and my_id fields.  It would be neat if we could also somehow flag why each record got picked, but that isn’t necessary.  </p>
  
  <p>It seems like something like this should be easy, but I have a hard time wrapping my head around how to pick from within a group_id.  Thanks for your help.</p>