question

Gelain avatar image
Gelain 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
group-bymaxlast
image-1.jpg (321.4 KiB)
1 comment
10 |1200

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 ·
maroon78b avatar image
maroon78b answered
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
10 |1200

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

maroon78b avatar image
maroon78b answered
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
10 |1200

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

Gelain avatar image
Gelain answered
Thank you!
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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