SQL bring result with repeated numbers of column all only the largest

0

Hello, I'm trying to get a data but I'm not able to filter it the way I need it, for example:

select a.cod, a.nome, b.pedido, b.versao from cliente a 
inner join pedido b On a.cod= b.cod
order by b.pedido

Now the problem one version has more than one type

I'm following the example of how the result is coming:

This was the only result that now brings me if I put the budget if the budget is null the person puts the start date and end date. More when I put it now it brings everything away disregarding the command

There it comes right if you put the quote.  If you put it by start and end date in the variables it returns everything without considering the last version.

    
asked by anonymous 15.05.2018 / 22:59

3 answers

1

I think you have an error in your scrip since, seemingly , you compare the order code with the client code in join , see inner join pedido b On a.cod= b.cod

You can add AND to your join which will filter through MAX version, see

select 
    a.cod, 
    a.nome, 
    b.pedido, 
    b.versao 
from cliente a 
    inner join pedido b On a.cod= b.codigo_cliente AND b.versao = (select max(pedido.versao) from pedido where pedido.codigo_cliente = a.id)
order by b.pedido

For reference I made an example in SQL Fiddle

EDIT

What I'm seeing wrong are checks with isnull , in them you say that if the field is null, it returns itself ( ISNULL (@vORCV_COD , @vORCV_COD) ) does not make sense of it.

Try the following query:

DECLARE
    @vORCV_COD INT ,
    @vDAT_INI CHAR(10),
    @vDAT_FIM CHAR(10)

SET @vORCV_COD =  null-- 333829
SET @vDAT_INI = '20180405' --data inicial
SET @vDAT_FIM = '20180501' -- data final

SELECT 
    A.ORCV_COD, 
    A.VEOV_VAL,
    A.VEOV_QTT_VER ,
    A.VEOV_PER_MARLUC,
    E.PARC_COD,
    A.PEDS_COD,
    I.PEDS_DAT_CAD,
    A.VEOV_DAT_CAD,
    V.VEND_NOM_FAN,
    B.AORC_OBS,
    B.AORC_DAT_CAD,
    C.USUA_NOM,
    D.TAOR_NOM,
    F.STOV_NOM,
    ISNULL(G.PARC_NOM_RAZ, J.PAOV_NOM_EMP) AS 'RAZAO SOCIAL',
    ISNULL(H.OROV_NOM, 'ORIGEM NÃO INDICADA') AS 'ORIGEM'
FROM TCOM_VERORV A 
    LEFT JOIN TCOM_ANDORC B         ON A.ORCV_COD = B.ORCV_COD 
    LEFT JOIN TCOM_TIPANDORC D      ON D.TAOR_COD = B.TAOR_COD
    INNER JOIN TCOM_ORCVEN E        ON E.ORCV_COD  = A.ORCV_COD
    INNER JOIN TCOM_STAORV F        ON  E.STOV_COD = F.STOV_COD
    LEFT JOIN TACE_USUARIO C        ON C.USUA_COD  = B.USUA_COD          
    INNER JOIN TCOM_VENDEDOR V      ON V.VEND_COD = E.VEND_COD
    LEFT JOIN TCOM_PARCEIRO G       ON G.PARC_COD = E.PARC_COD
    LEFT JOIN TCOM_ORIORV H         ON H.OROV_COD = E.OROV_COD
    LEFT JOIN TCOM_PEDSAI I         ON I.PEDS_COD = A.PEDS_COD
    LEFT JOIN TCOM_PARORV J         ON A.ORCV_COD = J.ORCV_COD
WHERE (1=1)
    AND ISNULL(A.ORCV_COD, 0) = ISNULL (@vORCV_COD , ISNULL(A.ORCV_COD, 0)) 
    AND A.VEOV_QTT_VER = (SELECT MAX(X.VEOV_QTT_VER) FROM TCOM_VERORV X WHERE ISNULL(X.ORCV_COD, 0) = ISNULL(A.ORCV_COD, 0))
    AND CONVERT(CHAR(10), ORCV_DAT_CAD , 112) BETWEEN ISNULL (@vDAT_INI, CONVERT(CHAR(10), ORCV_DAT_CAD , 112)) AND ISNULL (@vDAT_FIM, CONVERT(CHAR(10), ORCV_DAT_CAD , 112))

Explanation:

In addition to clearing the indentation, the only thing I actually changed was where in%, if it is null the variable it should compare with itself, and if the field is null, it will compare 0 with 0.

    
16.05.2018 / 03:12
1

What I understand is that for each request you should return the line with the latest version. There are some ways to get this result; here's one of them:

-- código #1
with PedidoClassificado as (
SELECT cod, pedido, versao,
       seq= row_number() over (partition by pedido order by versao desc)
  from pedido
)
SELECT B.cod, A.nome, B.pedido, B.versao
  from cliente as A 
       inner join PedidoClassificado as B on A.cod = B.cod
  where B.seq = 1;
    
16.05.2018 / 00:28
0

Try using the SQL WHERE command as below:

select 
     a.cod, 
     a.nome, 
     b.pedido, 
     b.versao 
from cliente a 
inner join pedido b 
   ON a.cod= b.cod
where b.versao = 3
order by b.pedido;
    
15.05.2018 / 23:17