mysql过虑最大时间,获取行由最新时间的MySQL过滤

I have a question of a complex query and I don't know how to write it.

I have the next table:

+---------------+-----------+----------+--------------------------+

|id_incidencia | id_alarma | idaction | concat(fecha, ' ', hora) |

+---------------+-----------+----------+--------------------------+

| 18488 | 551 | 1 | 2016-08-29 14:55:47 |

| 18491 | 551 | 7 | 2016-08-29 15:02:47 |

| 18493 | 551 | 6 | 2016-08-29 15:37:39 |

| 18495 | 551 | 7 | 2016-08-29 15:41:27 |

| 19263 | 551 | 6 | 2016-09-16 15:17:27 |

| 19429 | 5151 | 5 | 2016-09-19 09:01:17 |

| 19447 | 5151 | 5 | 2016-09-19 09:25:23 |

| 19500 | 5151 | 5 | 2016-09-20 09:38:11 |

| 19532 | 5151 | 5 | 2016-09-23 15:02:50 |

| 19526 | 5151 | 6 | 2016-09-23 11:23:27 |

| 19528 | 5151 | 6 | 2016-09-23 11:28:01 |

| 19527 | 5151 | 7 | 2016-09-23 11:25:18 |

| 19529 | 5151 | 7 | 2016-09-23 11:28:08 |

+---------------+-----------+----------+--------------------------+

I'd like to have the next result from a query:

idaction | id_alarma | datetime

---------+-----------+---------

7 | 551 | 2016-08-29 15:41:27

7 | 5151 | 2016-09-23 11:28:08

That's means that I want for each group of id_alarma value, I want to get the last row with the most recently time and it's value of idaction. The problem is that when I perform the next query, the value in idaction is not the same that match with the datetime field.

The query that I mention is:

SELECT a.id,

nia.idaction,

MAX(CONCAT(nia.fecha, ' ', nia.hora))

FROM nectar_incidencias_alarma nia

INNER JOIN alarmas a

ON a.id=nia.id_alarma

WHERE nia.idaction IN (6,7)

GROUP BY a.id

解决方案

To retrieve the correct value of idaction, you could use group_concat in combination with substring_index. Note that you don't really need to join the table alarmas, as you have all you need in nectar_incidencias_alarma:

SELECT id_alarma,

SUBSTRING_INDEX(GROUP_CONCAT(idaction

ORDER BY CONCAT(fecha, ' ', hora) DESC), ',', 1)+0 id_action,

MAX(CONCAT(fecha, ' ', hora))

FROM nectar_incidencias_alarma

WHERE idaction IN (6,7)

GROUP BY id_alarma