SQL query in zabbix database?

Question

SQL

Good afternoon, I'm trying to get a table with the names of hosts and the last value of the data element for these hosts from the Zabbix database in one query, in sql the newbie turned out to be the following query:

SELECT hosts.name,hosts_groups.hostid,items.itemid,history_text.value,history_text.clock
FROM hosts
inner JOIN hosts_groups ON (hosts.hostid=hosts_groups.hostid AND hosts_groups.groupid = "77") (получаем ID хостов из группы 77)
inner JOIN items ON (hosts.hostid=items.hostid AND items.name = "CertExpDate_complex")  (Получаем ID элементов данных с нужным именем
inner JOIN history_text ON history_text.itemid=items.itemid order by clock DESC limit 500; (Получаем значения искомых элементов данных

In my case, it is not possible to take the last value of each data element by host, if I
put limit 1 at the end of the request, then the result will be truncated to display information for 1 host. And I need to apply LIMIT 1 to the CLOCK column, take the largest value from there - 1 record and apply it to all other hosts in accordance. Tell me in what direction would it be correct to implement this in general? Look towards removing duplicates of identical rows by columns, or is there a way to enter the limit 1 parameter for a specific column during query assembly?

Answer 1

In your case, you need to look towards max(history_text.clock) and use
group by hosts.name,hosts_groups.hostid,items.itemid,history_text.value