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.
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
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
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.
But still there is no way to know which of those rows is the last one for the day
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
But if all the rows have the same date (including a timestamp to ms) which row do you think SQL will choose?
Thanks a lot for the help. There is an error and I could not resolve it.
the error is because you have not given a name/alias for column 2
Thank you! It worked.
19 People are following this question.
