SQL bring result with repeated column numbers all only the largest

Question

Hello guys I’m trying to get a data but I’m not able to filter the way I need, 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 a version has more than one type

Below is the example of how the result is coming:

that was the result but only brings now if I put the budget if the budget is null the person puts the initial and final date. More when I put now it brings everything disregarding the command

Then it brings right if you put the quotation. If put by initial and final date in the variables it returns to bring everything without considering the latest version.

Answer 1

What I understood is that for each request you must return the line with the latest version. There are some ways to get this result; here is 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;

Answer 2

I believe you have a mistake in yours scrip for, apparently, you compare the order code with the client code on join, see inner join pedido b On a.cod= b.cod

You can add a AND in his join that 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 on SQL Fiddle

---

EDIT

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

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 fixing the indentation, the only thing I actually altered was the where us isnull, if the variable is null it must compare with itself, and if the field is null it will compare 0 with 0.

Answer 3

Try using the WHERE SQL 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;