title: SQL selecting from a particular duplicate row
body: <p>Hi all I have a sql query</p>
  
  <pre><code>SELECT  FKid1, FKID2, a, b, c
  from [source]
  where FKID1 = 3
  </code></pre>
  
  <p>which returns the following data set so
  (hope formatting holds) </p>
  
  <pre><code>   FKID1   FKID2   A    B    C
      3   40297    0      0   2
      3   40297    0      100 1
      3   40325    0      0   2
      3   40325    0      0   3
      3   40325    0      10  -1
      3   40348    0      10  3
      3   40391    0      10  -1
      3   40392    0      10  -1
      3   40501    0      10  -1
      3   40501    0      0   2
  </code></pre>
  
  <p>I'm trying to improve this query so that if there are 2 rows with duplicate FKID1 and FKID2 values, it will pick the column B value from a particular row as follows...</p>
  
  <p>if there is a row with C = -1 use the B value in this row and ignore others. 
  if there are 2 rows with C &lt;> -1 then pick the MAX(B) value.
  For rows that are not duplicated, return as normal. </p>
  
  <p>IE the results should look as follows...</p>
  
  <pre><code>  FKID1   FKID2   A    B    C
      3   40297    0      100 1
      3   40325    0      10  -1
      3   40348    0      10  3
      3   40391    0      10  -1
      3   40392    0      10  -1
      3   40501    0      10  -1
  </code></pre>
  
  <p>the correct values selected for the B column and no dupes.</p>
  
  <p>We have a solution at the moment, but I think it's overcomplicated and wondered if anyone had any ideas? </p>
  
  <p>Thanks.</p>