Search code examples
sqldb2

DB2 - Return the smallest datetime within the Select

I have the following SQL statement in BD2 that returns the table below

SELECT
c.CREATEDATE AS "Data e Hora do Alerta"
,c.SENDFROM  AS "Enviado Por"   
,c.SENDTO AS "Para"
,c.SUBJECT AS "Assunto"
FROM  M.LOG c
WHERE c.SENDFROM  = '[email protected]' 
AND c.SUBJECT  = 'ALERTA - SLA estourado'
AND c.ID  = '4355070'

enter image description here

However, I only need the smallest date and time found, highlighted in yellow in the printout

I filtered out the c.ID = '4355070' for testing only.

I have several IDs that have N lines, the idea would be to bring the smallest date/time value of each ID

I've already tried using GROUP BY , but the "To" column has different values

I need to get the first date/time of the first alert issued and "To" who it was sent to

Thanks

Solution

  • Returns only one (arbitrary) row for the same (ID, CREATEDATE).

    SELECT
  c.CREATEDATE AS "Data e Hora do Alerta"
, c.SENDFROM  AS "Enviado Por"   
, c.SENDTO AS "Para"
, c.SUBJECT AS "Assunto"
FROM
(
  -- Your original SELECT statement emulation
  SELECT 
    *
  , ROW_NUMBER () OVER (PARTITION BY ID ORDER BY CREATEDATE) AS RN_
  FROM 
  (
    VALUES
      (10, CURRENT TIMESTAMP    , '[email protected]', '11', 'ALERTA - SLA estourado')
    , (10, CURRENT TIMESTAMP    , '[email protected]', '12', 'ALERTA - SLA estourado')
    , (10, CURRENT TIMESTAMP + 1, '[email protected]', '13', 'ALERTA - SLA estourado')

    , (20, CURRENT TIMESTAMP    , '[email protected]', '21', 'ALERTA - SLA estourado')
    , (20, CURRENT TIMESTAMP    , '[email protected]', '22', 'ALERTA - SLA estourado')
    , (20, CURRENT TIMESTAMP + 1, '[email protected]', '23', 'ALERTA - SLA estourado')
  ) c (ID, CREATEDATE, SENDFROM, SENDTO, SUBJECT)
  WHERE c.SENDFROM  = '[email protected]' 
  AND c.SUBJECT  = 'ALERTA - SLA estourado'
) c
WHERE RN_ = 1
    Data e Hora do Alerta Enviado Por Para Assunto
    2023-09-25-10.51.23.804000 [email protected] 11 ALERTA - SLA estourado
    2023-09-25-10.51.23.804000 [email protected] 21 ALERTA - SLA estourado