How to select smaller record by field

1

How can I display the least factor by ID_ACORDO?

SELECT  LIGACOES.ID 
        ,ACORDO.ID_ACORDO
        ,ACORDO.DATA as 'DATA_ACORDO'
        ,LIGACOES.time_of_contact AS 'DATA_LIGACAO'
        ,ACORDO.data-LIGACOES.time_of_contact as FATOR

FROM SISCOB_acordo_itau_pf_varejo AS ACORDO

INNER JOIN
DWH_Developer.ASPECT_CONTACT_EVENTS AS LIGACOES ON (LIGACOES.account_number = ACORDO.id_contr)

where ACORDO.DATA>= LIGACOES.time_of_contact 
    
asked by anonymous 07.07.2015 / 00:00

1 answer

1

I believe this to be min with Group by

    SELECT  LIGACOES.ID 
        ,ACORDO.ID_ACORDO
        ,ACORDO.DATA as 'DATA_ACORDO'
        ,LIGACOES.time_of_contact AS 'DATA_LIGACAO'
        ,MIN(ACORDO.data-LIGACOES.time_of_contact) as FATOR

FROM SISCOB_acordo_itau_pf_varejo AS ACORDO

INNER JOIN
DWH_Developer.ASPECT_CONTACT_EVENTS AS LIGACOES ON (LIGACOES.account_number = ACORDO.id_contr)

where ACORDO.DATA>= LIGACOES.time_of_contact 
GROUP BY LIGACOES.ID 
        ,ACORDO.ID_ACORDO
        ,ACORDO.DATA 
        ,LIGACOES.time
    
07.07.2015 / 04:41