title: Indicate a row that cause an abnormal case (SQL)
body: <p>I have a result as below using the following script:</p>
  <pre><code>SELECT 
      id, (2022 - age) yearId, age, [value],
      CASE 
          WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY [age]) = 0 
              THEN 'Base' 
          WHEN [value] &gt; LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
              THEN 'Increasing'
          WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
              THEN 'No Change'
          ELSE 'Decreasing'
      END AS [Order]
  FROM Test
  </code></pre>
  <p><a href="https://i.stack.imgur.com/x8Fxr.png" rel="nofollow noreferrer">Values</a></p>
  <p>And I manage to get a group of ids with an id causing a &quot;flip: decreasing and then increasing or the other way around&quot; as:</p>
  <p><a href="https://i.stack.imgur.com/Pl2oR.png" rel="nofollow noreferrer">Abnormal Case</a></p>
  <p>Now I want to print out the same result as above but with a column indicates the row that cause the flip, something like this (the row causes the flip should be place at the top of each partition):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Id</th>
  <th>age</th>
  <th>value</th>
  <th>flip</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>4</td>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>0</td>
  <td>5</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>4</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>3</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>5</td>
  <td>3</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>6</td>
  <td>4</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Thank you!</p>