Thread: Help with a JOIN.

Help with a JOIN.

From

Paul Linehan

Date:

20 July 2014, 15:13:01

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;

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

Re: Help with a JOIN.

From

Ken Benson

Date:

20 July 2014, 16:48:39

>>> 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;

>>>

>>> -----------+--------------------+------------+----------------+------------+-----------------------+----------------

>>> 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

Re: Help with a JOIN.

From

Paul Linehan

Date:

20 July 2014, 20:08:23

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

Re: Help with a JOIN.

From

Paul Linehan

Date:

21 July 2014, 01:25:46

[[KenB]] You're asking for the MAX(comments_comment) …

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;

The result I want is

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

Mob: 00 353 86 864 5772

Re: Help with a JOIN.

From

Paul Linehan

Date:

23 July 2014, 12:47:28

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

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;

I'm getting this

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 ====================

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 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

Mob: 00 353 86 864 5772

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

Re: Help with a JOIN.

From

Ken Benson

Date:

23 July 2014, 17:11:30

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

but on running the query

[[KenB]] AND x.ticket_id=z.ticket_id

;

I'm getting this

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;

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 ====================

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 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

Mob: 00 353 86 864 5772

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

Re: Help with a JOIN.

From

David G Johnston

Date:

23 July 2014, 17:24:31

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.

Re: Help with a JOIN.

From

Mike Swierczek

Date:

23 July 2014, 17:30:38

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
>

Re: Help with a JOIN.

From

Kevin Grittner

Date:

23 July 2014, 23:05:21

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