# question

## 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
image-1.jpg (321.4 KiB)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
`ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ..) AS RowNum` Then select where RowNum = 1
0 Likes 0 ·

·
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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.