I need to order a table by two columns.
This is the table
The field estado can have only three different values: Pendiente, Gestionada or Archivada.
I need to show first all items with estado = "Pendiente" order by fecha, and then all items with estado = "Archivado""
This is my current query:
SELECT
alerta.id as id,
alerta.id_usuario as id_usuario,
alerta.id_tipo_alerta as id_tipo_alerta,
alerta.latitud as latitud,
alerta.longitud as longitud,
alerta.descripcion as descripcion,
alerta.estado as estado,
alerta.fecha as fecha,
alerta.leida as leida,
tip.tipo_alerta as tipo_alerta
FROM tb_alertas alerta
LEFT JOIN tb_tipos_alertas tip ON alerta.id_tipo_alerta = tip.id
WHERE alerta.estado = 'Gestionada' OR alerta.estado = 'Archivada'
ORDER by alerta.fecha, alerta.estado DESC
What do I need to change in the query?
>Solution :
Use the FIELD() function to specify the ordering of the estado column.
ORDER BY FIELD(alerta.estado, 'Pendiente', 'Archivado', 'Gestionada'), alerta.fecha