title: Mariadb Increment counter with ORDER BY
body: <p>I Have table with description
  Table Name : mt_user
  column : id , name</p>
  <p>Records</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">Tom</td>
  </tr>
  <tr>
  <td style="text-align: left;">10</td>
  <td style="text-align: center;">Carren</td>
  </tr>
  <tr>
  <td style="text-align: left;">30</td>
  <td style="text-align: center;">Jessy</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Then I Query:</p>
  <pre><code>   select r.id,
          @rownum := @rownum + 1 as rownum
      from mt_user r
       cross join
          (SELECT
              @rownum := 0
          ) params
      order by r.id desc;
  </code></pre>
  <p>the result is :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">rownum</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">30</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: left;">10</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>But if the query :</p>
  <pre><code>   select r.id,
          @rownum := @rownum + 1 as rownum
      from mt_user r
       cross join
          (SELECT
              @rownum := 0
          ) params
      order by r.id asc;
  </code></pre>
  <p>the result is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">rownum</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">10</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">30</td>
  <td style="text-align: center;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>the question is, how can I achieved the result like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">id</th>
  <th style="text-align: center;">rownum</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">10</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">30</td>
  <td style="text-align: center;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>but with order by r.id desc</p>
  <p>because I trying the query in mariadb version 10.5.9, it have no problem.
  it act strange on mariadb version 10.6.10 or 10.9.2</p>