doubt max sql

Asked

Viewed 31 times

-1

I have a question about sql, to with a query that I want to bring the most recent answer of an object, only that is always coming the history, I tried sub query, and etc, max, but it did not work, someone to help?

SELECT DISTINCT 
       c.contrato, 
       ca.razao_social, 
       cnpj, 
       ar.descricao, 
       oa.descricao,
       CASE ac.situacao
           WHEN '1'
           THEN 'SemPendencia'
           WHEN '2'
           THEN 'ComPendencia'
           WHEN '3'
           THEN 'não se aplica'
       END AS Situacao,
       ac.data_inclusao
--(
    --SELECT DISTINCT TOP 1 (ac3.data_inclusao) from analise_contrato ac3 where ac3.codigo = ac.codigo order by ac3.data_inclusao desc) as datanova
--  (select *
--    FROM Protocolo_documentos_entrega pde2
--         LEFT JOIN protocolo_documentos pd2 ON pd2.codigo = pde2.cod_protocolo_documentos
--         LEFT JOIN protocolo p2 ON p2.codigo = pd2.cod_protocolo
--         JOIN Contrato c2 ON c2.codigo = p2.cod_contrato
--         JOIN Contratada ca2 ON ca2.codigo = c2.cod_contratada
--         JOIN Analise_contrato ac2 ON ac2.cod_contrato = c.codigo
--         JOIN Area_responsavel ar2 ON ar2.codigo = c2.cod_area
--         JOIN Objeto_analise oa2 ON oa2.codigo = ac2.cod_objeto_analise
--    WHERE c2.contrato = c.contrato
--          AND pde2.codigo = pde.codigo
--          AND ac2.codigo = ac.codigo
--          AND ac.data_inclusao = ac2.data_inclusao
--) AS DataInclusao
FROM Protocolo_documentos_entrega pde
     LEFT JOIN protocolo_documentos pd ON pd.codigo = pde.cod_protocolo_documentos
     LEFT JOIN protocolo p ON p.codigo = pd.cod_protocolo
     JOIN Contrato c ON c.codigo = p.cod_contrato
     JOIN Contratada ca ON ca.codigo = c.cod_contratada
     JOIN Analise_contrato ac ON ac.cod_contrato = c.codigo
     JOIN Area_responsavel ar ON ar.codigo = c.cod_area
     JOIN Objeto_analise oa ON oa.codigo = ac.cod_objeto_analise
WHERE c.cod_contratante = '152'
      AND pde.STATUS = 0
      AND c.STATUS = '0'
      AND c.contrato = '003/Renner'
GROUP BY pde.codigo, 
         c.contrato, 
         ca.razao_social, 
         ca.cnpj, 
         ar.descricao, 
         oa.descricao, 
         ac.situacao, 
         ac.data_inclusao, 
         c.codigo, 
         ac.codigo
ORDER BY oa.descricao;

inserir a descrição da imagem aqui

sql sql-server
  • 1

    You just want the first entry? If so, why not use LIMIT 1

  • no, at first, I wanted to come one of each object (Description field), ex, has 3 licenses Anvisa, I want him to consider a top 1 of the latest

  • 2

    At least for me it is difficult to understand the problem. I recommend making a minimum verifiable example. You will have more chances to answer your question. See instructions here: https://answall.com/help/minimal-reproducible-example . In this other issue, also of SQL, the author made an EMV: https://answall.com/questions/498179/como-fazer-um-merge-no-sql-quando-a-vari%C3%a1vel-key-if-repeat-in-one-of-the-tables . Could be an example.

  • take a look at this other question and see if it helps: https://answall.com/questions/498987/retr-valor-m%C3%a1ximo-de-table-no-Join-mysql/498997#498997

1 answer

-1

Dude, if I understand correctly, instead of doing the Join from the JOIN Parse_contract ac table, you need to work with Outerapply.

It would look something like this:

Outer apply (Select Top 1 ac.data_inclusao
             From   Analise_contrato AC
             Where 1=1
             And ac.cod_contrato = c.codigo
             Order by AC.data_inclusao Asc) Dt_include

Then there in select, would need to put Dt_include.data_inclusions.

Maybe this topic here can also help you: /questions/13731/diferença-entre-cross-apply-e-outer-apply

Browser other questions tagged sql sql-server

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