title: SQL/Postgres: Get Single ACTIVE=Y Record from Group, ELSE Latest Date Record from Group
body: <p>Using <strong>Postgres</strong>:</p>
  <p>My <code>AGREEMENT_T</code> lists records that belong to a particular <code>agreement_group_id</code>. At any time, within a Group, either 1 is <code>ACTIVE=Y</code>, or 0 (at most 1).</p>
  <pre><code>ID   AGREEMENT_GROUP_ID  ACTIVE  FIELD1  FIELD2 STATUS_ACTION_DATE
  ------------------------------------------------------------------
  1    55                  N       45      50     01-Oct-2021 00:03:05
  2    55                  Y       78      70     30-Sep-2021 03:04:05
  3    100                 N       44      4      02-Oct-2021 04:00:01
  4    101                 N       1       2      02-Oct-2021 04:01:05
  5    101                 N       0       1      05-Oct-2021 11:13:54
  6    102                 Y       3       3      05-Oct-2021 11:47:45
  7    102                 N       4       4      05-Oct-2021 11:48:49
  </code></pre>
  <p>I need to get a result that lists Agreement Group ID paired with</p>
  <ol>
  <li>If <code>ACTIVE=Y</code> is available within a Group, then that Agreement ID</li>
  <li>Else, the ID with the latest <code>STATUS_ACTION_DATE</code> for that Group</li>
  </ol>
  <p>ex.</p>
  <pre><code>55   2
  100  3
  101  5
  102  6
  </code></pre>
  <p>So far, I can pull up the latest <code>STATUS_ACTION_DATE</code> using Postgres' <code>array_agg(..)[1]</code> as follows,</p>
  <pre><code>SELECT agreement_group_id, (array_agg(id ORDER BY status_action_date DESC))[1] AS val
  FROM ets.agreement_t 
  GROUP BY agreement_group_id 
  ORDER BY agreement_group_id
  </code></pre>
  <p>But is there a quick way to integrate that with finding <code>ACTIVE=Y</code>, maybe without using an extra <code>SELECT EXISTS</code>? Also something that performs well.</p>