title: How to make a SQL "IF-THEN-ELSE" statement
body: <p>I've seen other questions about SQL If-then-else stuff, but I'm not seeing how to relate it to what I'm trying to do. I've been using SQL for about a year now but only basic stuff and never this. </p>
  
  <p>If I have a SQL table that looks like this</p>
  
  <pre><code>|  Name | Version | Category | Value | Number |
  |:-----:|:-------:|:--------:|:-----:|:------:|
  | File1 | 1.0     | Time     |   123 |      1 |
  | File1 | 1.0     | Size     |   456 |      1 |
  | File1 | 1.0     | Final    |   789 |      1 |
  | File2 | 1.0     | Time     |   312 |      1 |
  | File2 | 1.0     | Size     |   645 |      1 |
  | File2 | 1.0     | Final    |   978 |      1 |
  | File3 | 1.0     | Time     |   741 |      1 |
  | File3 | 1.0     | Size     |   852 |      1 |
  | File3 | 1.0     | Final    |   963 |      1 |
  | File1 | 1.1     | Time     |   369 |      2 |
  | File1 | 1.1     | Size     |   258 |      2 |
  | File1 | 1.1     | Final    |   147 |      2 |
  | File2 | 1.1     | Time     |   741 |      2 |
  | File2 | 1.1     | Size     |   734 |      2 |
  | File2 | 1.1     | Final    |   942 |      2 |
  | File3 | 1.1     | Time     |   997 |      2 |
  | File3 | 1.1     | Size     |   997 |      2 |
  | File3 | 1.1     | Final    |   985 |      2 |
  </code></pre>
  
  <p>How can I write a SQL IF, ELSE statement that creates a new column called "Replication" that follows this rule:</p>
  
  <pre><code>A = B + 1 when x = 1
  else 
  A = B
  
  where A = the number we will use for the next Number
  B = Max(Number)
  x = Replication count (this is the number of times that a loop is executed. x=i)
  </code></pre>
  
  <p>The results table will look like this:</p>
  
  <pre><code>|  Name | Version | Category | Value | Number | Replication |
  |:-----:|:-------:|:--------:|:-----:|:------:|:-----------:|
  | File1 | 1.0     | Time     |   123 |      1 |           1 |
  | File1 | 1.0     | Size     |   456 |      1 |           1 |
  | File1 | 1.0     | Final    |   789 |      1 |           1 |
  | File2 | 1.0     | Time     |   312 |      1 |           1 |
  | File2 | 1.0     | Size     |   645 |      1 |           1 |
  | File2 | 1.0     | Final    |   978 |      1 |           1 |
  | File1 | 1.0     | Time     |   369 |      1 |           2 |
  | File1 | 1.0     | Size     |   258 |      1 |           2 |
  | File1 | 1.0     | Final    |   147 |      1 |           2 |
  | File2 | 1.0     | Time     |   741 |      1 |           2 |
  | File2 | 1.0     | Size     |   734 |      1 |           2 |
  | File2 | 1.0     | Final    |   942 |      1 |           2 |
  | File1 | 1.1     | Time     |   997 |      2 |           1 |
  | File1 | 1.1     | Size     |   997 |      2 |           1 |
  | File1 | 1.1     | Final    |   985 |      2 |           1 |
  | File2 | 1.1     | Time     |   438 |      2 |           1 |
  | File2 | 1.1     | Size     |   735 |      2 |           1 |
  | File2 | 1.1     | Final    |   768 |      2 |           1 |
  | File1 | 1.1     | Time     |   786 |      2 |           2 |
  | File1 | 1.1     | Size     |   486 |      2 |           2 |
  | File1 | 1.1     | Final    |   135 |      2 |           2 |
  | File2 | 1.1     | Time     |   379 |      2 |           2 |
  | File2 | 1.1     | Size     |   943 |      2 |           2 |
  | File2 | 1.1     | Final    |   735 |      2 |           2 |
  </code></pre>
  
  <p><strong>EDIT:</strong> Based on the answer by Sean Lange, this is my 2nd attempt at a solution:</p>
  
  <pre><code>SELECT COALESCE(MAX)(Number) + CASE WHEN Replication = 1 then 1 else 0, 1) FROM Table
  </code></pre>
  
  <p><em>The <code>COALESCE</code> is in there for when there is no value yet in the Number column.</em></p>