select in an oracle to return the newest row

Question

I have a ticketing system (itsm) and I need to return the newest record from a table among many other records of the same ticket.

On the picture above, you can have a view of the output of select (I will paste it below). Realize that PBTI_WORKORDER_ID column shows the ticket ID and what I am trying to do is just show the greatest request_id (the first row on image).

The select is:

SELECT 
MAX(REQUEST_ID),
(PBTI_WORKORDER_ID),
PBTI_IDREQUISICAO,
TO_CHAR(SECS_TO_DATE(PBTI_DTABERTURA),'DD/MM/YYYY HH24:MI:SS') AS DATA_CRIACAO,
PBTI_GRUPOSUPORTEATUAL                                         AS GRUPO_ATUAL,
TO_CHAR(SECS_TO_DATE(PBTI_DATAENTRADAGRUPO),'DD/MM/YYYY')      AS DATA_ENTRADA,
TO_CHAR(SECS_TO_DATE(PBTI_DATASAIDAGRUPO),'DD/MM/YYYY')        AS DATA_SAIDA_GRUPO,
PBTI_PROXIMOGRUPOSUPORTE                                       AS PROXIMO_GRUPO,
REQUEST_ASSIGNEE,
CATEGORIZATION_TIER_1,
CATEGORIZATION_TIER_2,
CATEGORIZATION_TIER_3,
CUSTOMER_ORGANIZATION,
PBTI_MCU_ORG
FROM PBTI_TABELA_INDICADORES
WHERE PBTI_GRUPOSUPORTEATUAL = 'CENTRAL HD - TRATAMENTO'
GROUP BY (REQUEST_ID),
(PBTI_WORKORDER_ID),
(PBTI_IDREQUISICAO),
(PBTI_DTABERTURA),
(PBTI_GRUPOSUPORTEATUAL),
(PBTI_DATAENTRADAGRUPO),
(PBTI_DATASAIDAGRUPO),
(PBTI_PROXIMOGRUPOSUPORTE),
(REQUEST_ASSIGNEE),
(CATEGORIZATION_TIER_1),
(CATEGORIZATION_TIER_2),
(CATEGORIZATION_TIER_3),
(CUSTOMER_ORGANIZATION),
(PBTI_MCU_ORG)
ORDER BY PBTI_DATAENTRADAGRUPO DESC;

The MAX (used on select clause) should only bring the first row, does not?

tks

The MAX() used this way will give you the max(ID) from all the selected rows, but doesn't filter the rest of the data to be from that particular row. To do that you need to filter to just that record in your WHERE clause — Michael Broughton, Oct 30 '15 at 19:55

score 2 · Accepted Answer · answered Oct 30 '15 at 19:54

One method to accomplish this is to generate a set of data consisting of the max request_ID for each ticket (B). Then, join that set back to the base set (A) with all records, thereby using the join to only retain the relevant "newest" record per ticket / request.

Something like this..

SELECT A.* 
FROM PBTI_TABELA_INDICADORES A
INNER JOIN  (SELECT max(request_ID) Request_Id, PBTI_WorkORder_Id, PBTI_IDREQUISICAO
             FROM PBTI_TABELA_INDICADORES
             GROUP BY PBTI_WorkORder_Id, PBTI_IDREQUISICAO) B
  on A.Request_Id = B.Request_Id
 and A.PBTI_WorkORder_Id = B.PBTI_WorkORder_Id
 and A.PBTI_IDREQUISICAO = B.PBTI_IDREQUISICAO

select in an oracle to return the newest row

1 Answers1