CODEWITHSUNDEEP
postgresql
BlackOctober
BlackOctober

Reputation: 41

Need some help in creating a query in SQL?

ID|message| UpdateTime| TicketID| StaffID

10008;"Yes, it is!";"2012-04-15 16:15:00";1008;660
10013;"Thanks for swift reply!";"2012-04-15 17:15:00";1008;660

CAn u tell me when I write these 2 queries : 

Select MAX(UpdateTime) from TicketUpdate where ticketUpdate.id = 10008;    
Select MIN(UpdateTime) from TicketUpdate where ticketUpdate.id = 10008;

The output the same even in my database if have 2 different times. Can u tell me what could be the problem here?

Upvotes: 1

Views: 78

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

ID appears to be the unique identifier for this table. Instead, it looks like you want to use TicketID to find the max and min values per ticket.

For example, to find them for TicketID = 1008:

SELECT MAX(UpdateTime) FROM TicketUpdate WHERE TicketUpdate.TicketID = 1008;    
SELECT MIN(UpdateTime) FROM TicketUpdate WHERE TicketUpdate.TicketID = 1008;

Or in one query:

SELECT 
  MAX(UpdateTime) AS newest,
  MIN(UpdateTime) AS oldest
FROM TicketUpdate
WHERE TicketID = 1008;

To get the most recent and oldest for every individual TicketID, use a GROUP BY and omit the WHERE clause.:

SELECT 
  TicketID,
  MAX(UpdateTime) AS newest,
  MIN(UpdateTime) AS oldest,
FROM TicketUpdate
GROUP BY TicketID

If you query using ID, you will always get the same row since there appears to be only one value for each ID that uniquely identifies its row.

Upvotes: 1

Related Questions