title: Return one record per criteria
body: <p>I am struggling with a solution for getting one record per criteria.
  In the attached example, I would like to get one record per &quot;nbr&quot;.
  I have started with this, but I cannot get what I need.</p>
  <pre><code>    SELECT t2.*,
          CASE 
              WHEN t2.duplicate_flag=1 AND (t2.country=t2.country_active OR t2.country=t2.country_inactive) THEN 1 ELSE t2.duplicate_flag END nbr_match
  FROM
  (SELECT t1.*,
          CASE d.duplicate WHEN 1 THEN 1 ELSE 999 END duplicate_flag
  FROM [Sheet1$] t1
  JOIN
  (SELECT nbr, COUNT(*) as duplicate
  FROM [Sheet1$]
  GROUP BY nbr) d
  ON t1.nbr=d.nbr) t2
  </code></pre>
  <p>As I said, I would like to get one record per &quot;nbr&quot;.
  If I have one record per &quot;nbr&quot; it is OK and I will mark it with &quot;1&quot;. For the others that have duplicates (count(*)&gt;1), I will mark as &quot;99&quot; and then to modify these &quot;99&quot; based on conditions to get one record.
  These are the rules that I have to follow in order to get one record per &quot;nbr&quot;:</p>
  <pre><code>1. IF &quot;country&quot;=&quot;country_active&quot; return me that row (per &quot;nbr&quot;)
  2. ELSEIF &quot;country&quot;=&quot;country_inactive&quot; return me that row
  3. If I have two the same countries between the &quot;country&quot; and the &quot;country_active&quot; THEN look at the MAX &quot;expiration_date_active&quot; OR MAX &quot;expiration_date_inactive&quot; and return me that record.
  4. IF there is no match between &quot;country&quot;&lt;&gt;&quot;country_active&quot; AND &quot;country&quot;&lt;&gt;&quot;country_inactive&quot; return the column with the MAX date either from &quot;expiration_date_active&quot; OR &quot;expiration_date_inactive&quot;
  </code></pre>
  <p>The final table or output should look like:
  <a href="https://i.stack.imgur.com/CJwP8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CJwP8.png" alt="enter image description here" /></a></p>
  <p>Thank you in advance for help.</p>
  <p>Original data:</p>