title: Filtering out non-zero values unless the value is the only one within its group (SQL or R)
body: <p>I have a data set of record numbers, with multiple values in a column for each record. I would like to produce a table where within each group it returns the minimum non-negative, non-NA value. <em>But</em> if that negative number or NA corresponds to the only row for that record, then I would like it to return NA.</p>
  
  <pre><code>data &lt;- data.frame(record = c(1, 1, 1, 2, 3, 3, 4), value = c(-1, 2, 40, -10, 3, 4, NA))
  
  | record | value |
  ------------------
  |   1    |  -1   |
  |   1    |   2   |
  |   1    |   40  |
  |   2    |  -10  |
  |   3    |   3   |
  |   3    |   4   |
  |   4    |   NA  |
  ------------------
  </code></pre>
  
  <p>I would like to get</p>
  
  <pre><code>| record |  out  |
  ------------------
  |   1    |   2   |
  |   2    |  NA   |
  |   3    |   3   |
  |   4    |  NA   |
  ------------------
  </code></pre>
  
  <p>In R I have tried:</p>
  
  <pre><code>filter(data, value &gt;= 0 | is.na(value))
  data %&gt;% group_by(record) %&gt;% summarize(out = min(value))
  </code></pre>
  
  <p>In MySQL I have tried</p>
  
  <pre><code>select min(value) as out
  from data
  where (value &gt;= 0 or value is null))
  </code></pre>
  
  <p>However, both of these solutions drop record #2 from the table.</p>
  
  <p>How do I include a clause that detects the number of rows per record and doesn't eliminate single records?</p>