How to select smallest record by field

Asked

Viewed 246 times

1

How can I present the smallest 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
sql mysqli

  • You want to take the record that has the smallest value in the field ID_ACORDO, would it be this?

  • No, actually I would like the records that contain the smallest factor per agreement.

1 answer

1

I believe this is it , min com 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

  • It is not certain, because if I include the field LINKS.time_of_contact it does not correspond to the smallest factor.

  • For example, I selected an id according to specific, as you can see in the image. I need only the lowest-factor record http://i.stack.Imgur.com/5LqHg.png

  • In my example is the smallest FACTOR per AGREEMENT , to change this would be enough , I believe , change the grouping (GROUP BY).

  • Thanks Motta, but it’s hard! I’ve added another image that should go into more detail: http://i.stack.Imgur.com/Mamha.png. Can’t sort by the slightest factor and then always select the first record by ID_ACORDO?

  • I renewed the Solution , in my opinion is just a question of grouping , if I understand the problem.

Browser other questions tagged sql mysqli

You are not signed in. Login or sign up in order to post.