question

Gelain avatar image
Gelain asked

Generate last row in date by ID

Greetings, I need to generate a report to extract only the last balance per product on a certain date. It turns out that you have several balances on the same date. It would be a stock report. I made the following query: select idprd ,convert(varchar(10),DATAMOVIMENTO,103) ,SALDOFILIAL ,CUSTOMEDFILIAL ,totalfilial from TRELSLD AS A where IDPRD=83 Return: ID Date Amount Average Cost Total 83 21/02/2017 8487.0000 0.1383 1173.9731 83 21/02/2017 8486.0000 0.1383 1173.8348 83 21/02/2017 8485.0000 0.1383 1173.6965 83 21/02/2017 8484.0000 0.1383 1173.5582 I need to return only the last line per product (ID): ID Date Amount Average Cost Total 83 21/02/2017 8484.0000 0.1383 1173.5582 Thanks if anyone can help.
maxtoplast
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.

To know which is the last, we need to know what order they are in. Without an order they are just a set of 4 rows
1 Like 1 ·
supriyan avatar image
supriyan answered
Please check this code SELECT TOP 1 IDPRD, CONVERT(varchar(10),DATAMOVIMENTO,103) ,SALDOFILIAL, CUSTOMEDFILIAL, totalfilial FROM TRELSLD AS A WHERE IDPRD=83 ORDER BY DATAMOVIMENTO DESC
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
Thanks Kev and Supriyan for the help. Better detail my need: DATE RECCREATEDON IDPRD AMOUNT AVERAGE COST TOTAL ---------- ----------------------- ----------- ------------------ ------------------- ------------------------ 22/02/2017 2017-03-03 10:00:53.000 83 1511.0000 0.0657 99.2710 22/02/2017 2017-03-03 10:00:53.000 83 1510.0000 0.0657 99.2053 23/02/2017 2017-03-03 10:00:53.000 83 1511.0000 0.0657 99.2710 23/02/2017 2017-03-03 10:00:53.000 83 1510.0000 0.0657 99.2053 23/02/2017 2017-03-03 10:00:53.000 83 1508.0000 0.0657 99.0739 23/02/2017 2017-03-03 10:00:53.000 83 1499.0000 0.0657 98.4826 I searched with the RECCREATEDON column, but at the same time it has several values. I consider that I need the final balance of the day for several products that are in the same table. Greetings.
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.

But still there is no way to know which of those rows is the last one for the day
2 Likes 2 ·
Thomas_Cruse avatar image
Thomas_Cruse answered
why not use the row_number windows function, order it by date desc and grab row 1. Like this. with a as ( select ROW_NUMBER() over(partition by a.idprd order by a.DATAMOVIMENTO desc) as rownumber, convert(varchar(10),a.DATAMOVIMENTO,103), a.SALDOFILIAL, a.CUSTOMEDFILIAL, a.totalfilial from TRELSLD A ) select * from a where rownumber = 1
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.

But if all the rows have the same date (including a timestamp to ms) which row do you think SQL will choose?
1 Like 1 ·
Gelain avatar image
Gelain answered
Thanks a lot for the help. There is an error and I could not resolve it.

image-1.jpg (230.8 KiB)
image-2.jpg (182.1 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.

the error is because you have not given a name/alias for column 2
0 Likes 0 ·
Gelain avatar image
Gelain answered
Thank you! It worked.
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.