This should be faster, yet:
SELECT status, count(*)
SELECT DISTINCT ON (ticket_number) status
ORDER BY ticket_number, datetime DESC
GROUP BY 1;
Assuming datetime is defined NOT NULL.
Select first row in each GROUP BY group?
If there are many rows per ticket, one of these techniques will be ...
WITH cte AS ( SELECT DISTINCT
FIRST_VALUE(status) OVER (PARTITION BY ticket_number
ORDER BY datetime DESC) last_status
FROM test )
SELECT last_status, COUNT(last_status)
GROUP BY last_status
for to obtain needed ...
my trick is, make temporary table with field category and units then u make schedule at night to counting it n save to your temporary table, then ur just read that table with just 12 row in it, very faster, but ur count H-1, u need liitle trick againt to add current day ...
This query will update the oldest records that exceed the count of 500, so you keep 500 newest records for orgid = 1
set `delete` = true
where orgid = 1 AND id IN (
SELECT id FROM A where orgid = 1
order by createdate DESC