title: Getting the most common value based on another value?
body: <p>I have a table with country data, a table with COVID measures and a table that links the two, with data that looks a little like this:</p>
  <pre><code>country:
  +----+--------------+
  | id | country_name |
  +----+--------------+
  | 01 | Afghanistan  |
  | 02 | Albania      |
  | 03 | Algeria      |
  |... | ...          |
  +----+--------------+
  
  covid_measures:
  +------+--------------+---------------+
  | m_id | category     | subcategory   |
  +------+--------------+---------------+
  | 1.1  | Individual   | Hand Hygiene  |
  | 1.2  | Individual   | Wear Mask     |
  | 1.3  | Individual   | Distancing    |
  | 2.1  | Surveillance | Case Detect.  |
  | 2.2  | Surveillance | Contact Trace |
  | ...  | ...          | ...           |
  +------+--------------+---------------+
  
  link_table:
  +----+------+-------+
  | id | m_id | date  |
  +----+------+-------+
  | 01 | 1.1  | 21/01 |
  | 01 | 3.2  | 24/01 |
  | 01 | 8.2  | 25/01 |
  | 01 | 3.3  | 28/01 |
  | 01 | 1.1  | 28/01 |
  |... | ...  | ...   |
  +----+------+-------+
  </code></pre>
  <p>I want to get the <code>m_id</code> and count that was the most used by each country. The output should look something like this:</p>
  <pre><code>+--------------+------------+--------------+-------+
  | country_name | category   | subcategory  | count |
  +--------------+------------+--------------+-------+
  | Afghanistan  | Individual | Wear Mask    | 12    |
  | Albania      | Individual | Distancing   | 8     |
  | Algeria      | Travel     | Stay at home | 10    |
  | ...          | ...        | ...          | ...   |
  +--------------+------------+--------------+-------+
  </code></pre>
  <p>What I have so far counts the occurrence of each measure by country, but I am not sure how to get the most common from those by country.</p>
  <pre><code>SELECT country_name, category, subcategory, COUNT(*) AS count
  FROM link_table AS l
  JOIN country AS c ON l.id = c.id
  JOIN coid_measure AS cm ON cm.m_id = l.m_id
  GROUP BY country_name, category, subcategory
  ORDER BY country_name, COUNT(*) DESC;
  </code></pre>
  <p>This gives this:</p>
  <pre><code>+--------------+--------------+---------------+-------+
  | country_name | category     | subcategory   | count |
  +--------------+--------------+---------------+-------+
  | Afghanistan  | Individual   | Wear Mask     | 12    |
  | Afghanistan  | Individual   | Distancing    | 10    |
  | Afghanistan  | Travel       | Close Border  | 9     |
  | Afghanistan  | Surveillance | Contact Trace | 8     |
  | ...          | ...          | ...           | ...   |
  +--------------+--------------+---------------+-------+
  </code></pre>