Function MAX only 1 (one) record on Oracle

Question

Could someone give me a hand in using Oracle’s Function MAX()???

I need to do a query that returns only the last record of a history table, but I need to return 3 (three) columns of this record -> Id, Ticket_id and Queue_id, but if I use the query as follows the query is not executed due to an error in GROUP BY.

To run the query I should add the QUEUE_ID column in GROUP BY as follows:

But this way the result does not return the last record by MAX(ID) and yes returns 2 records, as below:

And I just need the last entry, in case it would be 53474 with Queue_id 22.

Does anyone know how to create this type of query on Oracle?

Answer 1

Assuming that there is only one entry with the maximum value of the desired column (for the conditions placed), the question can be solved in this way:

SELECT ID, TICKET_ID, QUEUE_ID
FROM TICKET_HISTORY
WHERE ID IN (
  SELECT MAX(ID) 
  FROM TICKET_HISTORY
  WHERE HISTORY_TYPE_ID = 27 AND STATE_ID = 2 AND 
  TICKET_ID = 1290
);

Answer 2

Try it this way:

SELECT MAX(ID), TICKET_ID, QUEUE_ID
FROM TICKET_HISTORY
WHERE HISTORY_TYPE_ID = 27 AND STATE_ID = 2 AND TICKET_ID = 1290
ORDER BY TICKET_ID DESC LIMIT 1;