title: Filter multiple records on right table into 1 record of left table
body: <p>So I have a translations table which holds various representations of a place name. I join this table on the <code>placeId</code>, where this table serves as the right side (the left side contains info about the <code>place</code>).</p>
  
  <p>However, joining on placeId results in possibly more translations, see table below. The contents of preferredName/shortName/historicName are all 0 or 1. There is no rule though that each translation should have at least 1 record with <code>preferredName=1</code>.</p>
  
  <p>So what I end up with is: how can I select only 1 of these translations, in particular:</p>
  
  <ul>
  <li>if record exists with <code>preferredName=1</code>: use this</li>
  <li>else if record exists with <code>shortName=1</code>: use this</li>
  <li>else if neither is true (so both are 0) then pick that record</li>
  </ul>
  
  <p>.</p>
  
  <pre><code>+---------------+---------+------------------+---------------+-----------+--------------+
  | translationId | placeId |  alternateName   | preferredName | shortName | historicName |
  +---------------+---------+------------------+---------------+-----------+--------------+
  |          4832 |     554 | 'New York'       |             1 |         0 |            0 |
  |          4833 |     554 | 'NY'             |             0 |         1 |            0 |
  |          4834 |     554 | 'New York City'  |             0 |         0 |            0 |
  +---------------+---------+------------------+---------------+-----------+--------------+
  </code></pre>
  
  <p>Any clue? It basically boils down to filtering multiple matches on the right table to 1 record on the left table.</p>