topic getting most recent record from table in Application Development and Automation Discussions

getting most recent record from table

Former Member — Fri, 13 Feb 2009 20:23:11 GMT

I need to get the most recent record in an ODS. Sorting by calday will not work for me since the records may not be entered by date order. This is what I have now, ordered by calday. I need to change it to get the most recent record. Any suggestions?

select * from /BIC/ACFI_O00100 into WA_FLDS

up to 1 rows

where /BIC/FACID = SOURCE_FIELDS-/BIC/FACID

and CALYEAR = SOURCE_FIELDS-CALDAY+00(04)

order by CALDAY DESCENDING.

endselect.

Re: getting most recent record from table

Sandra_Rossi — Fri, 13 Feb 2009 23:19:25 GMT

No other choice than adding a field with TIME or sequence information for example (or maybe with native sql but I don't think you'd like to, and it's not advised)

Re: getting most recent record from table

nkr1shna — Fri, 13 Feb 2009 23:21:45 GMT

Hi Uday,

Please dont use "ORDER BY" clause in your select statement. This will alter order in which record got created. Another tip is whether you are table contains date and time record got written? If so use this field in your ORDER BY.

Best Regards

Krishna

Re: getting most recent record from table

Former Member — Sat, 14 Feb 2009 04:51:06 GMT

hi,

Use change document object to track the manner the records created in the table. The respective values are stored in the CDPOS and CDHDR.

PS The prerequisite for this entries to be created is that the check box for change document must be check in the data element level.

Check this wiki for step by step procedure for change document.

[Change document|https://wiki.sdn.sap.com/wiki/display/ABAP/Change+Document]

Thanks

Sharath

Re: getting most recent record from table

Sandra_Rossi — Sat, 14 Feb 2009 14:21:58 GMT


This will alter order in which record got created.

To make things clear to everybody, you MUST NOT assume that without ORDER BY, you get records by the order they are created ! It completely depends on database server (RDBMS) logic : for example, if you create 2 indexes on the table, the RDBMS may choose any index and any order. The only solution to have records sorted is to use ORDER BY (or SORT at ABAP level).

Re: getting most recent record from table

Former Member — Sat, 14 Feb 2009 18:56:04 GMT

Let's also remember that this question has been asked many times before and that there is no generally satisfactory answer.

Rob

Re: getting most recent record from table

Former Member — Sun, 15 Feb 2009 06:30:11 GMT

it is better to get the data in internal tabel and the fliter the most recent one from the same.