Mysql criteria for the order of Rows

The SQL standard defines that, if it is not explicitly stated as part of the query, the order of the results is random , in the sense that the engine is free to choose em> the order in which you will return the records of the query.

Indeed, some engines can be predicted the order in which a query will return the results, but this can vary at any time, for example, if you upgrade your engine to a version more recent, if you change some configuration parameter of the engine or if you use the same engine in a different operating system.

It can even change according to the conditions of the data itself, because most engines, for example, will use statistical information about the data to choose the best execution plan and, believe me, this execution plan will be the which will determine the order of the data.

That's why, when changing any condition, include a new table in the join , or change the list of columns, and you have experienced a variation in the order of the results, which is correct according to the specification.

Therefore, if you require the data in a particular order, there is the clause order by . With this, you indicate exactly in which order you want the engine to return the data, using as many columns or expressions as necessary to obtain the exact order you are looking for. You can also sort ascending ( asc ), which is default , or decententemente ( desc ).

For example.

select *
  from tabla
 order by columna1, columna2, columna3;

select *
  from tabla
 order by columna1 asc, columna2 + columna3 desc;

select *
  from tabla
 order by columna1 asc, substring(columna2, 1, 2) desc
        , case when columna3 = 1 then columna4 else columna5 end desc;

Edit

I see that what you are looking for is not only sorting , but the calculation of disponible is correct.

First, I explain that it is failing because the calculation occurs before the sort.

The correct way to solve this, in the standard, is to use window functions , but these are not available until recent versions of mysql (I believe that from 8, but I'm not sure).

SELECT   movimientoId
       , DATE_FORMAT(fecha, '%d/%m/%y') fecha
       , descripcion
       , nombre
       , IF(tipo=1, monto, NULL) deposito
       , IF(tipo=-1, monto, NULL) retiro
       , sum(monto * tipo) over (order by movimientoId rows between unbounded preceding and current row) disponible
  FROM movimiento 
       INNER JOIN cliente ON movimiento.clienteid = cliente.clienteid
 WHERE movimiento.cuentaid = 'CAJA-001' 
   AND movimiento.fecha BETWEEN '2018-11-01' AND '2018-11-01'
 order by movimientoId

Now, if you use an older version, I do not have mysql at hand to try, but I guess that by using this strange engine it supports a clause order by inside of sub-queries, which generally ignores, except if you put a clause limit , I would say that the solution is in:

• Put the query that brings the data into a sub-query, without the calculation of the available, but with a clause order by
• Make a query about the previous sub-query, and taking advantage of the return of the ordered result, on this perform the calculation (there is no guarantee that it works, or that the syntax is correct).

Use it at your own risk:

SET @dispo=0;
select   x.*
       , @dispo := @dispo + coalesce(deposito, 0) - coalesce(retiro, 0) disponible
  from (SELECT   movimientoId
               , DATE_FORMAT(fecha, '%d/%m/%y') fecha
               , descripcion
               , nombre
               , IF(tipo=1, monto, NULL) deposito
               , IF(tipo=-1, monto, NULL) retiro
          FROM movimiento 
               INNER JOIN cliente ON movimiento.clienteid = cliente.clienteid
         WHERE movimiento.cuentaid = 'CAJA-001' 
           AND movimiento.fecha BETWEEN '2018-11-01' AND '2018-11-01'
         order by movimientoId
         limit 25000
        ) as X