Function MAX only 1 (one) record in Oracle

0

Can anyone help me with 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 that record - > Id, Ticket_id and Queue_id, but if I use the query as below, the query does not execute because of an error in GROUP BY.

ForthequerytorunIshouldaddtheQUEUE_IDcolumninGROUPBYasfollows:

ButthiswaytheresultdoesnotreturnthelastrecordbytheMAX(ID)andinsteaditreturns2records,asbelow:

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

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

    
asked by anonymous 19.03.2018 / 23:32

2 answers

1

Assuming that there is only one entry with the maximum value of the desired column (for the conditions placed), you can solve the question like this:

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
);
    
20.03.2018 / 00:31
0

Try this:

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;
    
19.03.2018 / 23:58