Re: Help with a JOIN.

>>> From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Paul Linehan
>>> Sent: Sunday, July 20, 2014 3:04 AM
>>> To: Gerald Cheves
>>> Cc: pgsql-novice(at)postgresql(dot)org
>>> Subject: [NOVICE] Help with a JOIN.
>>>
>>>
>>> Hi all,
>>>
>>> I've got a problem with a query I'm trying. I've got it
>>> working with a CTE, but I'd like to do it as a JOIN.
>>>
>>> I've given the DDL and DML at the end of this post.
>>>
>>> I have two tables: tickets and comments. I need to run a
>>> report that shows me which ticket is "neglected" meaning
>>> that there has not been a comment in X amount of time or
>>> not been modified for X amount of time. The important field
>>> here is comments_timestamp.
>>> First, I'll show the CTE and the result (correct) that I want.
>>>
>>> WITH t2 AS
>>> (
>>> SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date
>>> FROM comment c
>>> WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
>>> GROUP BY ticket_id
>>> )
>>> SELECT t2.ticket_id, t.ticket_description, t2.c_max_date,
>>> t2.c_max_date AS cte_c_max_date, c.comment_id, c.comments_comment,
>>> c.comments_timestamp AS com_c_max_date
>>> FROM ticket t, t2, comment c
>>> WHERE t.ticket_id = t2.ticket_id
>>> AND t2.c_max_date = c.comments_timestamp;
>>>
>>> ticket_id | ticket_description | c_max_date | cte_c_max_date | comment_id | comments_comment | com_c_max_date
>>> -----------+--------------------+------------+----------------+------------+-----------------------+----------------
>>> 3 | ticket 3 | 1171379773 | 1171379773 | 10 | comment 2 on ticket 3 | 1171379773
>>> 4 | ticket 4 | 1167484540 | 1167484540 | 15 | comment 3 on ticket 4 | 1167484540
>>>
>>> Now, I'm trying to run a JOIN like this
>>>
>>> SELECT t.ticket_id, t.ticket_description, x.c_max_date, x.c_comment
>>> FROM
>>> (
>>> SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date, MAX(comments_comment) AS c_comment
>>> FROM comment c
>>> WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
>>> GROUP BY ticket_id
>>> )
>>> AS x INNER JOIN ticket AS t ON t.ticket_id = x.ticket_id;
>>> But my result are the following
>>>
>>> ticket_id | ticket_description | c_max_date | c_comment
>>> -----------+--------------------+------------+-----------------------
>>> 3 | ticket 3 | 1171379773 | comment 4 on ticket 3
>>> 4 | ticket 4 | 1167484540 | comment 4 on ticket 4
>>>

[[KenB]] You're asking for the MAX(comments_comment) … which is what you're getting.
What you actually want is the comment that comes from the row that has the MAX(comments_timestamp)
I think you need an additional join statement:

SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.coments_comment
FROM
(
SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date
FROM comment c
WHERE c.comments_timestamp < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000
GROUP BY ticket_id
)
AS x INNER JOIN ticket AS t ON t.ticket_id = x.ticket_id
INNER JOIN comment AS z ON z.comments_timestamp = x.c_max_date;

>>> The problem is that the comment is not correct for the given ticket date - it's
>>> picking up what appears to be the last comment alphabetically for the given
>>> ticket_id - I want the comment (obviously) associated with the date (which
>>> is the latest comments_timestamp for the given ticket_id where the ticket
>>> hasn't been modified for 60000000 seconds (approx. 2 years).
>>> Any help in getting this query to work as a JOIN would be appreciated.
>>> I know that the date stuff is a bit wierd - I'll fix that later. Finally, for a
>>> bonus, I would also have to do this for MySQL if anyone also has
>>> any clues about how to do it for that server, that would be great.
>>>
>>> ============ DDL and DML for tables ============
>>>
>>>
>>> CREATE TABLE IF NOT EXISTS ticket
>>> (
>>> ticket_id INT NOT NULL,
>>> ticket_description VARCHAR(25),
>>> PRIMARY KEY (ticket_id)
>>> );
>>>
>>>
>>> CREATE TABLE IF NOT EXISTS comment
>>> (
>>> comment_id INT NOT NULL,
>>> ticket_id INT NOT NULL,
>>> comments_comment VARCHAR(45) NULL,
>>> comments_timestamp INT NULL,
>>> PRIMARY KEY (comment_id)
>>> );
>>>
>>> INSERT INTO ticket VALUES(1, 'ticket 1');
>>> INSERT INTO ticket VALUES(2, 'ticket 2');
>>> INSERT INTO ticket VALUES(3, 'ticket 3');
>>> INSERT INTO ticket VALUES(4, 'ticket 4');
>>> INSERT INTO ticket VALUES(5, 'ticket 5');
>>>
>>>
>>> INSERT INTO comment VALUES(1, 1, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:31'));
>>> INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:34'));
>>> INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:36'));
>>> INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:37'));
>>> INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:35'));
>>> INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:34'));
>>> INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:38'));
>>> INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:01'));
>>> INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:12'));
>>> INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2007-02-13 15:16:13'));
>>> INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2003-06-30 15:17:23'));
>>> INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:12'));
>>> INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-10-30 15:18:07'));
>>> INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 14:15:04'));
>>> INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2006-12-30 13:15:40'));
>>> INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2004-12-09 12:15:31'));
>>> INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:32'));
>>> INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:33'));
>>> INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:34'));
>>> INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:35'));
>>>
>>>
>>> ======== DDL and DML for the tables ==========
>>>
>>>
>>> TIA and rgs,
>>>
>>> Paul...
>>>
>>>
>>> --
>>>
>>> linehanp(at)tcd(dot)ie
>>>
>>> Mob: 00 353 86 864 5772