getting most recent record from table

[Former Member]

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.

[Sandra_Rossi]

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)

[nkr1shna]

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

[Sandra_Rossi]

>

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).

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

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