Re: Help with a JOIN.

Paul Linehan <linehanp(at)tcd(dot)ie> wrote:

> I have two tables - ticket and comment (DDL and DML below) and I
> want to get the latest comment on a ticket which hasn't been
> modified for some arbitrary period (in this example, 2 years (60M
> seconds)).

A simple description like that, coupled with the DDL and data,
makes life really easy for someone to help.  :-)  Try this:

SELECT DISTINCT ON (t.ticket_id)
    t.ticket_id,
    t.ticket_description,
    c.comments_timestamp AS c_max_date,
    c.comments_comment
  FROM ticket AS t
  JOIN comment c ON c.ticket_id = t.ticket_id
  WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
  ORDER BY
    t.ticket_id,
    c.comments_timestamp DESC;

SELECT DISTINCT ON gives you one row for each distinct combination
of values in parentheses, and when there are duplicates it keeps
the first one based on the ORDER BY clause.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company