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