How to avoid timestamp issue in a long query?
- by pingi
Hi,
I have the following 2 tables:
items:
id int primary key
bla text
events:
id_items int
num int
when timestamp without time zone
ble text
composite primary key: id_items, num
and want to select to each item the most recent event (the newest 'when').
I wrote an request, but I don't know if it could be written more efficiently.
Also on PostgreSQL there is a issue with comparing Timestamp objects:
2010-05-08T10:00:00.123 == 2010-05-08T10:00:00.321
so I select with 'MAX(num)'
Any thoughts how to make it better? Thanks.
SELECT i.*, ea.* FROM items AS i JOIN
( SELECT t.s AS t_s, t.c AS t_c, max(e.num) AS o FROM events AS e JOIN
( SELECT DISTINCT id_item AS s, MAX(when) AS c FROM events GROUP BY s ORDER BY c ) AS t
ON t.s = e.id_item AND e.when = t.c GROUP BY t.s, t.c ) AS tt
ON tt.t_s = i.id JOIN events AS ea ON ea.id_item = tt.t_s AND ea.cas = tt.t_c AND ea.num = tt.o;
