clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926599 fiddles created (21859 in the last week).

CREATE TABLE test ( ticket_number INT, datetime TIMESTAMP WITHOUT TIME ZONE, status VARCHAR(8)); INSERT INTO test VALUES (1, '2020-08-03 03:52:58.048196', 'replied'), (1, '2020-08-02 19:55:49.121455', 'new'), (2, '2020-07-30 03:52:58.048196', 'pending'), (2, '2020-07-28 20:15:41.213842', 'replied'), (2, '2020-07-26 03:52:58.482911', 'new'), (3, '2020-06-17 19:55:49.394628', 'closed'), (3, '2020-06-14 03:52:58.513141', 'replied'), (3, '2020-06-11 19:55:49.242859', 'new'), (4, '2020-05-14 07:13:50.527481', 'new'), (5, '2020-05-13 11:24:38.558921', 'new'); SELECT * FROM test;
10 rows affected
ticket_number datetime status
1 2020-08-03 03:52:58.048196 replied
1 2020-08-02 19:55:49.121455 new
2 2020-07-30 03:52:58.048196 pending
2 2020-07-28 20:15:41.213842 replied
2 2020-07-26 03:52:58.482911 new
3 2020-06-17 19:55:49.394628 closed
3 2020-06-14 03:52:58.513141 replied
3 2020-06-11 19:55:49.242859 new
4 2020-05-14 07:13:50.527481 new
5 2020-05-13 11:24:38.558921 new
 hidden batch(es)


WITH cte AS ( SELECT DISTINCT ticket_number, FIRST_VALUE(status) OVER (PARTITION BY ticket_number ORDER BY datetime DESC) last_status FROM test ) SELECT last_status, COUNT(last_status) FROM cte GROUP BY last_status
last_status count
closed 1
pending 1
replied 1
new 2
 hidden batch(es)