Ordering table and grouping by two columns-CodePudding

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?

CodePudding user response：

Use the FIELD() function to specify the ordering of the estado column.

ORDER BY FIELD(alerta.estado, 'Pendiente', 'Archivado', 'Gestionada'), alerta.fecha