How to rewrite long query?

Question

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;

EDIT: had bad data, sorry, my bad, however thanks for finding better SQL query

Answer 1

SELECT  (i).*, (e).*
FROM    (
        SELECT  i,
                (
                SELECT  e
                FROM    events e
                WHERE   e.id_items = i.id
                ORDER BY
                        when DESC
                LIMIT 1
                ) e
        FROM    items i
        ) q

Answer 2

If you're using 8.4:

select * from (
  select item.*, event.*,
         row_number() over(partition by item.id order by event."when" desc) as row_number
  from items item
       join events event on event.id_items = item.id
) x where row_number = 1

Answer 3

For this kind of joins, I prefer the DISTINCT ON syntax (example). It's a Postgresql extension (not SQL standard syntax), but it comes very handy:

SELECT DISTINCT ON (it.id) 
it.*, ev.*
FROM items it, events ev
WHERE ev.id_items = it.id
ORDER by it.id, ev.when DESC;

You can't beat that, on terms of simplicity and readability.

That query assumes that every item has at least one event. If not, and if you want all events, you'll need an outer join:

SELECT DISTINCT ON (it.id) 
it.*, ev.*
FROM items it LEFT JOIN events ev
ON ev.id_items = it.id
ORDER BY it.id, ev.when DESC;

BTW: There is no "timestamp issue" in Postgresql, perhaps you should change the title.