title: Select first row of group with criteria
body: <p>I have a table in this format:</p>
  
  <pre><code>FieldA   FieldB   FieldC
  1111     ABC      X
  1111     DEF      Y
  1111     GHI      X
  2222     JKL      Y
  2222     MNO      X
  3333     PQR      U
  3333     STT      U
  </code></pre>
  
  <p>I want to select one FieldB per FieldA with preference to X in FieldC (if there no X, pick another one).</p>
  
  <p>I've tried using the RANK function with PARTITION BY but I find it too inconsistent and I have now reached a wall.</p>
  
  <p>My output would look like this:</p>
  
  <pre><code> FieldA   FieldB   FieldC
      1111     ABC      X
      2222     MNO      X
      3333     PQR      U
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>Select
  rank() over (partition by Field3 order by Field1),
  Field,1 Field2, Field3
  FROM table
  ORDER BY Field1, Field3
  </code></pre>
  
  <p>I'm guessing I'd need to put that query inside a sub-query...</p>