SQL query to get latest timestamp

Admin 1
Viewed 135

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 = 'bhavya.aggarwal@gmail.com' and status = 'Open' and c.updatedat = max(c.updatedat)

What query should i run to meet this condition.

greatest-n-per-group postgresql sql
2 Answers

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 = 'bhavya.aggarwal@gmail.com' AND status = 'Open'
ORDER BY c.updatedat DESC
LIMIT 1
user_124928900 
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 = 'bhavya.aggarwal@gmail.com' AND STATUS = 'Open' AND c.updatedat = SELECT MAX(w1.updatedat) FROM comments w1 WHERE w1.ticketid = t.id
user_37491930
Related