Column of unknown subquery

Indeed it is so, what happens is that the ORDER is the last thing that "processes" the engine, so in that instance the alias of the calculated column does exist, however at the time of processing the filters the WHERE said column has not yet been resolved, so it gives you an error.

MySql in particular allows you to do the following in these cases:

SELECT (CASE WHEN EXISTS (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE()) THEN TRUE ELSE FALSE END) AS TieneNovedadHoy 
      FROM sicor_seguimientos 
      INNER JOIN jurisdicciones 
         ON jurisdicciones.id = sicor_seguimientos.IdJurisdiccion 
      WHERE usuario ='anderson' 
      HAVING TieneNovedadHoy IS TRUE    
      ORDER BY TieneNovedadHoy DESC

HAVING TieneNovedadHoy IS TRUE in other dialects of SQL is invalid because the having is only valid together with clauses GROUP BY , but as I said, MySQL allows it. The most ANSI SQL form would be:

Repeat subquery in WHERE

SELECT (CASE WHEN EXISTS (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE()) THEN TRUE ELSE FALSE END) AS TieneNovedadHoy 
      FROM sicor_seguimientos 
      INNER JOIN jurisdicciones 
         ON jurisdicciones.id = sicor_seguimientos.IdJurisdiccion 
      WHERE (CASE WHEN EXISTS (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE()) THEN TRUE ELSE) is TRUE 
            AND usuario ='anderson' 
      ORDER BY TieneNovedadHoy DESC

Use a subquery

SELECT TieneNovedadHoy
    FROM (SELECT CASE WHEN EXISTS (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE()) THEN TRUE ELSE FALSE END AS TieneNovedadHoy 
                FROM sicor_seguimientos 
                INNER JOIN jurisdicciones 
                    ON jurisdicciones.id = sicor_seguimientos.IdJurisdiccion 
                WHERE usuario ='anderson'
        ) T
        WHERE T.TieneNovedadHoy IS TRUE
        ORDER BY T.TieneNovedadHoy DESC

Solve it with LEFT JOIN

I do not see that you can not do it this way too:

SELECT (CASE WHEN sicor_registros.IdSeguimiento IS NOT NULL THEN TRUE ELSE FALSE END) AS TieneNovedadHoy 
      FROM sicor_seguimientos 
      INNER JOIN jurisdicciones 
         ON jurisdicciones.id = sicor_seguimientos.IdJurisdiccion 
      LEFT JOIN sicor_registros
         ON sicor_registros.IdSeguimiento = sicor_seguimientos.id
         AND sicor_registros.fecha = CURDATE() 
      WHERE (CASE WHEN sicor_registros.IdSeguimiento IS NOT NULL THEN TRUE ELSE FALSE END) is TRUE 
            AND usuario ='anderson' 
      ORDER BY TieneNovedadHoy DESC

I managed to solve it.

Column aliases are not allowed in the WHERE clause because when it is executed the value of the column has not yet been determined.

The solution was:

SELECT sicor_strays. *, jurisdictions. AS name Jurisdiction_name, CASE WHEN EXISTS (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE ()) THEN TRUE ELSE FALSE END AS HasNovedadToday, (SELECT date FROM sicor_registers WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id ORDER BY date DESC LIMIT 1) As UltimaUpdate FROM sicor_seguimientos INNER JOIN jurisdicciones ON jurisdicciones.id = sicor_seguimientos.IdJurisdiccion WHERE (SELECT id FROM sicor_registros WHERE sicor_registros.IdSeguimiento = sicor_seguimientos.id AND fecha = CURDATE ()) is TRUE AND user = 'anderson' ORDER BY HaveNovedadToday DESC