asked
Select last line
Dear, How to extract the SALDOMOV by IDPRD according to the largest sequential for the various CODLOCs? Thank you very much. SELECT A.DATAMOVIMENTO , A.IDPRD , A.SEQUENCIAL , A.CODLOC , A.SALDOMOV , A.TOTALMOV , A.CUSTOMEDMOV , B.NOMEFANTASIA , C.CODTB3FAT , CASE WHEN CODTB3FAT = '001' THEN 'Medicamentos Hospitalares' WHEN CODTB3FAT = '002' THEN 'Materiais Hospitalares' ELSE C.CODTB3FAT END CLASSIFICAÇÃO FROM TRELSLD AS A INNER JOIN tproduto AS B ON A.idprd = B.idprd INNER JOIN TPRODUTODEF AS C ON A.IDPRD = C.IDPRD AND A.CODCOLIGADA = C.CODCOLIGADA WHERE A.CODFILIAL = 2 AND CODLOC IN ( 01.004, 01.001 ) AND a.IDPRD = 672 ORDER BY DATAMOVIMENTO ![alt text][1] [1]: /storage/temp/4126-image-1.jpg
`ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ..) AS RowNum` Then select where RowNum = 1
select DATAMOVIMENTO , IDPRD , SEQUENCIAL , CODLOC , SALDOMOV , TOTALMOV , CUSTOMEDMOV , NOMEFANTASIA , CODTB3FAT , CLASSIFICAÇÃO from ( SELECT A.DATAMOVIMENTO , A.IDPRD , A.SEQUENCIAL , A.CODLOC , A.SALDOMOV , A.TOTALMOV , A.CUSTOMEDMOV , B.NOMEFANTASIA , C.CODTB3FAT , CASE WHEN CODTB3FAT = '001' THEN 'Medicamentos Hospitalares' WHEN CODTB3FAT = '002' THEN 'Materiais Hospitalares' ELSE C.CODTB3FAT END CLASSIFICAÇÃO row_number() over (partition by datamovimento,idprd,codloc order by datamovimento asc, idprd asc, codloc desc 0 RN FROM TRELSLD AS A INNER JOIN tproduto AS B ON A.idprd = B.idprd INNER JOIN TPRODUTODEF AS C ON A.IDPRD = C.IDPRD AND A.CODCOLIGADA = C.CODCOLIGADA WHERE A.CODFILIAL = 2 AND CODLOC IN ( 01.004, 01.001 ) AND a.IDPRD = 672) aset where rn = 1 order by datamovimento,idprd,codloc assuming that you want the largest codloc value for each datamovimento and idprd set of values
select * from ( SELECT A.DATAMOVIMENTO , A.IDPRD , A.SEQUENCIAL , A.CODLOC , A.SALDOMOV , A.TOTALMOV , A.CUSTOMEDMOV , B.NOMEFANTASIA , C.CODTB3FAT , CASE WHEN CODTB3FAT = '001' THEN 'Medicamentos Hospitalares' WHEN CODTB3FAT = '002' THEN 'Materiais Hospitalares' ELSE C.CODTB3FAT END CLASSIFICAÇÃO row_number() over (partition by datamovimento,idprd,codoloc order by datamovimento asc, idprd asc, codoloc desc 0 RN FROM TRELSLD AS A INNER JOIN tproduto AS B ON A.idprd = B.idprd INNER JOIN TPRODUTODEF AS C ON A.IDPRD = C.IDPRD AND A.CODCOLIGADA = C.CODCOLIGADA WHERE A.CODFILIAL = 2 AND CODLOC IN ( 01.004, 01.001 ) AND a.IDPRD = 672) a where rn = 1
Thank you!
19 People are following this question.
