Обсуждение: 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
here is comments_timestamp.
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
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;
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
picking up what appears to be the last comment alphabetically for the given
is the latest comments_timestamp for the given ticket_id where the ticket
hasn't been modified for 60000000 seconds (approx. 2 years).
bonus, I would also have to do this for MySQL if anyone also has
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'));
>>> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Paul Linehan
>>> Sent: Sunday, July 20, 2014 3:04 AM
>>> To: Gerald Cheves
>>> Cc: pgsql-novice@postgresql.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@tcd.ie
>>>
>>> Mob: 00 353 86 864 5772
Funny how reading one's own email is helpful! :-)
The answer of course is
SELECT t.ticket_id, t.ticket_description, x.c_com_id, z.comments_comment
FROM
(
SELECT ticket_id, MAX(c.comment_id) AS c_com_id
FROM comment c
WHERE c.comments_timestamp < UNIX_TIMESTAMP(NOW() - INTERVAL 2 YEAR)
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.comment_id = x.c_com_id;
i.e. using the comment_id as the discriminator - which I actually *_wrote_*, but was
unable to apply until I'd reread my own email. Funny thing the mind (well, mine anyway! :-) ).
Thanks to the list and esp. Ken.
Rgs,
Paul...
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
[[KenB]] You're asking for the MAX(comments_comment) …
duplicated dates (see DDL and DML below).
With this data, the result of your query
SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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;
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
select ticket_id, comment_id, comments_timestamp
from comment where ticket_id IN (3, 4)
order by ticket_id, comment_id, comments_timestamp;
gives
ticket_id | comment_id | comments_timestamp
-----------+------------+--------------------
3 | 9 | 1007133330
3 | 10 | 1013613330
3 | 11 | 1088608530
4 | 14 | 1076685330
for ticket 4.
assume that comment_id is some sort of auto incrementing field - but I can't include
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:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
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;
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
from comment where ticket_id IN (3, 4)
order by ticket_id, comment_id, comments_timestamp;
I see the data has the following
ticket_id | comment_id | comments_timestamp
-----------+------------+--------------------
3 | 9 | 1007133330
3 | 10 | 1013613330
3 | 11 | 1088608530
4 | 14 | 1076685330
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:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
3 | 12 | 1089386130 <<-- matches on ticket 4Your query works perfectly for the data that I posted, however, I have data which has
[[KenB]] You're asking for the MAX(comments_comment) …<rest snipped>Thanks for that Ken - just another small question for the list though.
duplicated dates (see DDL and DML below).
With this data, the result of your query
SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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;is
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4The result I want isRunning this query on the comments table
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
select ticket_id, comment_id, comments_timestamp
from comment where ticket_id IN (3, 4)
order by ticket_id, comment_id, comments_timestamp;
gives
ticket_id | comment_id | comments_timestamp
-----------+------------+--------------------
3 | 9 | 1007133330
3 | 10 | 1013613330
3 | 11 | 10886085304 | 13 | 1070205330
4 | 14 | 10766853304 | 15 | 1089386130 <<-- matches on ticket 34 | 16 | 1151680530The problem is that the last date for ticket 3 matches a date (not the last)
for ticket 4.Is there any way that I can pick up only the latest ticket in this case - you may
assume that comment_id is some sort of auto incrementing field - but I can't includecomment_id in my x subquery. I've been experimenting, but to no avail.TIA and rgs,Paul...======== DDL and DML for my problem ====================
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:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
linehanp@tcd.ie
Mob: 00 353 86 864 5772
From: Paul Linehan [mailto:linehanp@tcd.ie]
Sent: Wednesday, July 23, 2014 2:47 AM
To: Ken Benson
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help with a JOIN.
Hi all (again),
Ah... the joys of the shifting spec... I had an issue over the weekend and received assistance from
the list. I thought the problem was solved, but in fact there was a "modification to requirements" which
means that I'm back at square one!
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)).
The result I want is this
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
but on running the query
SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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
[[KenB]] AND x.ticket_id=z.ticket_id
;
I'm getting this
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4
This is because when I run this query
select ticket_id, comment_id, comments_timestamp
from comment where ticket_id IN (3, 4)
order by ticket_id, comment_id, comments_timestamp;
I see the data has the following
ticket_id | comment_id | comments_timestamp
-----------+------------+--------------------
3 | 9 | 1007133330
3 | 10 | 1013613330
3 | 11 | 1088608530
3 | 12 | 1089386130 <<-- matches on ticket 4
4 | 13 | 1070205330
4 | 14 | 1076685330
4 | 15 | 1089386130 <<-- matches on ticket 3
4 | 16 | 1151680530
So my query is picking up the match between 3 (latest comments_timestamp) and a
timestamp on ticket 4 which is not the latest comments_timestamp for ticket 4.
What I would like is for my SQL to pick up the latest comments_timestamp by each ticket
and not the match between the latest comment on ticket 3 and one of the timestamps on
ticket 4.
I did think that I'd be able to use the comment_id field in the comment table, but as
it turns out, this is *_not_* indicative of the latest comment - comments may be modified
after they have been entered, so the largest value of comment_id for a given ticket
is not necessarily the last modification for that ticket.
Any assistance/ideas gratefully received.
Paul...
======== DDL and DML for my problem ====================
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:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
2014-07-20 17:51 GMT+01:00 Paul Linehan <linehanp@tcd.ie>:
[[KenB]] You're asking for the MAX(comments_comment) …<rest snipped>
Thanks for that Ken - just another small question for the list though.
Your query works perfectly for the data that I posted, however, I have data which has
duplicated dates (see DDL and DML below).
With this data, the result of your query
SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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;is
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4The result I want is
ticket_id | ticket_description | c_max_date | comments_comment
-----------+--------------------+------------+-----------------------
3 | ticket 3 | 1089386130 | comment 4 on ticket 3
4 | ticket 4 | 1151680530 | comment 4 on ticket 4Running this query on the comments table
select ticket_id, comment_id, comments_timestamp
from comment where ticket_id IN (3, 4)
order by ticket_id, comment_id, comments_timestamp;
gives
ticket_id | comment_id | comments_timestamp
-----------+------------+--------------------
3 | 9 | 1007133330
3 | 10 | 1013613330
3 | 11 | 10886085303 | 12 | 1089386130 <<-- matches on ticket 4
4 | 13 | 1070205330
4 | 14 | 10766853304 | 15 | 1089386130 <<-- matches on ticket 3
4 | 16 | 1151680530
The problem is that the last date for ticket 3 matches a date (not the last)
for ticket 4.Is there any way that I can pick up only the latest ticket in this case - you may
assume that comment_id is some sort of auto incrementing field - but I can't includecomment_id in my x subquery. I've been experimenting, but to no avail.
TIA and rgs,
Paul...
======== DDL and DML for my problem ====================
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:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30'));
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
Shouldn't the inner join between x and b ( sub query and comment ) include both the date and the ticketid? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-with-a-JOIN-tp5812094p5812526.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Comments below, as normal. On 07/23/2014 10:11 AM, Ken Benson wrote: > From: Paul Linehan [mailto:linehanp@tcd.ie] > Sent: Wednesday, July 23, 2014 2:47 AM > To: Ken Benson > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Help with a JOIN. > > > Hi all (again), > > Ah... the joys of the shifting spec... I had an issue over the weekend and received assistance from > the list. I thought the problem was solved, but in fact there was a "modification to requirements" which > means that I'm back at square one! > > > 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)). > > The result I want is this > > ticket_id | ticket_description | c_max_date | comments_comment > -----------+--------------------+------------+----------------------- > 3 | ticket 3 | 1089386130 | comment 4 on ticket 3 > 4 | ticket 4 | 1151680530 | comment 4 on ticket 4 > > > but on running the query > > > SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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 > ) I suspect part of the problem, or a related problem, is that your timestamp restriction is inside your inner select. Say you have a ticket 6 with comments in 2013, 2011, and 2010. Your inner select will return a row 6, 2011 for that ticket. I don't think that's what you want, from your description you only want tickets that don't have comments in the last two years, not the most recent comment that's at least two years old for each ticket. I believe instead you want your inner select to be: ( SELECT ticket_id, MAX(c.comments_timestamp) AS c_max_date FROM comment c GROUP BY ticket_id HAVING MAX(c.comments_timestamp) < extract(epoch FROM TIMESTAMP '2014-07-20') - 60000000 ) Good luck, Mike > 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 > [[KenB]] AND x.ticket_id=z.ticket_id > ; > > > I'm getting this > > ticket_id | ticket_description | c_max_date | comments_comment > -----------+--------------------+------------+----------------------- > 3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4 > 3 | ticket 3 | 1089386130 | comment 4 on ticket 3 > 4 | ticket 4 | 1151680530 | comment 4 on ticket 4 > > > > This is because when I run this query > > > select ticket_id, comment_id, comments_timestamp > from comment where ticket_id IN (3, 4) > order by ticket_id, comment_id, comments_timestamp; > > I see the data has the following > > ticket_id | comment_id | comments_timestamp > -----------+------------+-------------------- > 3 | 9 | 1007133330 > 3 | 10 | 1013613330 > 3 | 11 | 1088608530 > 3 | 12 | 1089386130 <<-- matches on ticket 4 > 4 | 13 | 1070205330 > 4 | 14 | 1076685330 > 4 | 15 | 1089386130 <<-- matches on ticket 3 > 4 | 16 | 1151680530 > > So my query is picking up the match between 3 (latest comments_timestamp) and a > timestamp on ticket 4 which is not the latest comments_timestamp for ticket 4. > > > What I would like is for my SQL to pick up the latest comments_timestamp by each ticket > and not the match between the latest comment on ticket 3 and one of the timestamps on > ticket 4. > > I did think that I'd be able to use the comment_id field in the comment table, but as > it turns out, this is *_not_* indicative of the latest comment - comments may be modified > after they have been entered, so the largest value of comment_id for a given ticket > is not necessarily the last modification for that ticket. > > > Any assistance/ideas gratefully received. > > > Paul... > > > > ======== DDL and DML for my problem ==================== > > 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:30')); > INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30')); > INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30')); > INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30')); > INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30')); > INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30')); > INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30')); > INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30')); > INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30')); > INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30')); > INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30')); > INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30')); > INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30')); > INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30')); > INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30')); > INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30')); > INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30')); > > > > 2014-07-20 17:51 GMT+01:00 Paul Linehan <linehanp@tcd.ie<mailto:linehanp@tcd.ie>>: > > [[KenB]] You're asking for the MAX(comments_comment) … > <rest snipped> > > Thanks for that Ken - just another small question for the list though. > Your query works perfectly for the data that I posted, however, I have data which has > duplicated dates (see DDL and DML below). > > With this data, the result of your query > > SELECT t.ticket_id, t.ticket_description, x.c_max_date, z.comments_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; > is > > ticket_id | ticket_description | c_max_date | comments_comment > -----------+--------------------+------------+----------------------- > 3 | ticket 3 | 1089386130 | comment 3 on ticket 4 <<=== matches a timestamp for ticket 4 > 3 | ticket 3 | 1089386130 | comment 4 on ticket 3 > 4 | ticket 4 | 1151680530 | comment 4 on ticket 4 > > The result I want is > > ticket_id | ticket_description | c_max_date | comments_comment > -----------+--------------------+------------+----------------------- > 3 | ticket 3 | 1089386130 | comment 4 on ticket 3 > 4 | ticket 4 | 1151680530 | comment 4 on ticket 4 > > > Running this query on the comments table > select ticket_id, comment_id, comments_timestamp > from comment where ticket_id IN (3, 4) > order by ticket_id, comment_id, comments_timestamp; > > gives > > ticket_id | comment_id | comments_timestamp > -----------+------------+-------------------- > 3 | 9 | 1007133330 > 3 | 10 | 1013613330 > 3 | 11 | 1088608530 > 3 | 12 | 1089386130 <<-- matches on ticket 4 > 4 | 13 | 1070205330 > 4 | 14 | 1076685330 > 4 | 15 | 1089386130 <<-- matches on ticket 3 > 4 | 16 | 1151680530 > > The problem is that the last date for ticket 3 matches a date (not the last) > for ticket 4. > Is there any way that I can pick up only the latest ticket in this case - you may > assume that comment_id is some sort of auto incrementing field - but I can't include > comment_id in my x subquery. I've been experimenting, but to no avail. > > TIA and rgs, > Paul... > > ======== DDL and DML for my problem ==================== > > 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:30')); > INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30')); > INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30')); > INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30')); > INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', extract(epoch FROM TIMESTAMP '2014-07-30 15:15:30')); > INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', extract(epoch FROM TIMESTAMP '2014-08-09 15:15:30')); > INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', extract(epoch FROM TIMESTAMP '2001-11-30 15:15:30')); > INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', extract(epoch FROM TIMESTAMP '2002-02-13 15:15:30')); > INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2004-06-30 15:15:30')); > INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30')); > INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', extract(epoch FROM TIMESTAMP '2003-11-30 15:15:30')); > INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', extract(epoch FROM TIMESTAMP '2004-02-13 15:15:30')); > INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', extract(epoch FROM TIMESTAMP '2004-07-09 15:15:30')); > INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2006-06-30 15:15:30')); > INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', extract(epoch FROM TIMESTAMP '2013-11-30 15:15:30')); > INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', extract(epoch FROM TIMESTAMP '2014-02-13 15:15:30')); > INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', extract(epoch FROM TIMESTAMP '2014-06-30 15:15:30')); > INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', extract(epoch FROM TIMESTAMP '2014-07-09 15:15:30')); > > > -- > > linehanp@tcd.ie<mailto:linehanp@tcd.ie> > > Mob: 00 353 86 864 5772 > > > > > -- > > linehanp@tcd.ie<mailto:linehanp@tcd.ie> > > Mob: 00 353 86 864 5772 >
Paul Linehan <linehanp@tcd.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