SQL query to get latest timestamp-CodePudding

I am working on this query where I have two tables ticket and comments. Now the condition is I need to fetch the latest note(timestamp) from the other table based on one field there ie: updatedAt. This is the query I am trying to get but getting errors.

SELECT  t.id, t.title, t.requesterEmail, t.createdAt, c.note , t.priority, t.assigneeEmail, t.status 
FROM ticket t inner join comments  c  on  c.ticketid  = t.id   
WHERE assigneeEmail = '[email protected]' and status = 'Open' and c.updatedat = max(c.updatedat)

What query should i run to meet this condition.

CodePudding user response：

Try using an ORDER BY clause combined with a LIMIT clause. The former will order your "updatedAt" values descendently, while the latter one will select only one row (the one having the highest "updatedAt" value):

SELECT  t.id, t.title, t.requesterEmail, t.createdAt, c.note , t.priority, t.assigneeEmail, t.status 
FROM       ticket   t 
INNER JOIN comments c  on  c.ticketid  = t.id   
WHERE assigneeEmail = '[email protected]' AND status = 'Open'
ORDER BY c.updatedat DESC
LIMIT 1

CodePudding user response：

SELECT  t.id, t.title, t.requesterEmail, t.createdAt, c.note , t.priority, t.assigneeEmail, t.status 
FROM ticket t 
  INNER JOIN comments  c  ON  c.ticketid  = t.id   
WHERE assigneeEmail = '[email protected]' AND STATUS = 'Open' AND c.updatedat = SELECT MAX(w1.updatedat) FROM comments w1 WHERE w1.ticketid = t.id