topic Re: Finding last Record from a table in Application Development and Automation Discussions

Finding last Record from a table

Former Member — Sat, 19 Mar 2005 02:33:21 GMT

Hi ,

What is the method to find the last inserted record from a database table.

Regards

AArun

Re: Finding last Record from a table

RichHeilman — Sat, 19 Mar 2005 03:23:15 GMT



data: index type i.
Describe table itab lines index.
read table itab index index.

Regards,

Rich Heilman

Re: Finding last Record from a table

Former Member — Sat, 19 Mar 2005 03:58:01 GMT

Rich's post is in regards to an internal table, not a database table.

Couple of ideas for a database table:

1) Some tables record the user, date, time in the data.

2) Create change documents or change pointers for the table.

3) Use table logging. Check the documentation at

http://help.sap.com/saphelp_470/helpdata/en/7e/c81ed552c511d182c50000e829fbfe/frameset.htm

Re: Finding last Record from a table

RichHeilman — Sat, 19 Mar 2005 13:48:36 GMT

oops, looks like I need to pay more attention.

Sorry,

Rich Heilman

Re: Finding last Record from a table

ssimsekler — Sat, 19 Mar 2005 21:10:26 GMT

Rich's example also applies for only internal tables of type "standard". And if during the execution there is any sorting mechanism for the internal table, it might not work properly either. So, handling this problem needs some work-around regarding to what is really needed.

Anyways, since the issue is about something different I jump to the relevant topic. Charles has signed to possible solutions.

For this, your table should contain some purpose-specific fields (e.g. timestamp). Timestamping may be needed twice for the first creation and for the last change.

Or if your table has a key field which takes its value from a number range object, you can get te record with the greatest number.

If your table has not either of these and is a standard table. Try coding into an exit to store a record in another customer table containing the key of the standard table record and the timestamp or an order number.

Considering SAP's backup-restore strategy, it seems at deeper infrastructure they store something more about this. However, they pertain to the technical layer.

Hope this much helps...

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

Re: Finding last Record from a table

Former Member — Sun, 20 Mar 2005 16:32:30 GMT

Any database table is automatically sorted by its key fields. So if you do a select of table in the descending order of the key fields up to 1 rows, then you will get the last record.

Re: Finding last Record from a table

Former Member — Sun, 20 Mar 2005 17:09:55 GMT

Hi Srinivas. The highest key is not necessarily the last one inserted. An example is an employee table keyed by SSN. Just because an employee has the highest SSN number does not mean that he/she was the last one inserted.

I am somewhat confused by what you mean by "automatically sorted by its key fields"? My understanding is that you cannot count on the order of the data returned from the data base manager unless you specify an order. Do you have a different understanding?

Charlie...<a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=jvmiygdzb%2fyqmtolryqkmq%3d%3d">[BC]</a>

Re: Finding last Record from a table

Former Member — Mon, 21 Mar 2005 04:20:59 GMT

Hi ,

Thanks all for your replys.

My actual requirement is that i have to access the most recent record from table MARDH , for a user entered range of material number and plant.

One method is to get all the records for this range into an internal table ,ordered by date in descending order and then access the first record for each material-plant combination and store it into another internal table.

Is there any better way of doing it as in this case unnecessary processing is required.

Regrads

Arun

Re: Finding last Record from a table

Former Member — Mon, 21 Mar 2005 04:58:12 GMT

There are two ways to find the last record from the table and to determine the best way is, for you to analyse where the processing needs to be done:

a) Database

b) Application layer

Application layer, Rich and Charles have already mentioned. In case of database layer, just to minimize few lines of code, you can do something similar to this :

data: lv_lfgja type mardh-lfgja.
data: lv_werks type werks_d.

select max( LFGJA )
  into lv_lfgja
  from mardh
 where werks = '3210'.

if sy-subrc = 0.
  write lv_lfgja.
else.
  write 'unsuccessful'.
endif.

In the above code, you will only get LFGJA or the fiscal period. In order to get other details, you would have to do something similar to this:

data: lv_lfgja type mardh-lfgja.
data: lv_werks type werks_d.

select single werks max( LFGJA )
         into (lv_werks, lv_lfgja)
         from mardh
        where werks = '3210'
        group by werks.

if sy-subrc = 0.
  write : lv_werks, lv_lfgja.
else.
  write 'unsuccessful'.
endif.

Regards,

Subramanian V.

Re: Finding last Record from a table

Former Member — Mon, 21 Mar 2005 05:05:00 GMT

Hi Arun,

Another possible solution is to check for the table CDHDR with the transaction name and Change object as Material or use the function module CHANGEDOCUMENT_READ_HEADERS to get the list of recently changed records. Maybe this wud help u out Arun.

Regards

Abhishek

Re: Finding last Record from a table

Former Member — Mon, 21 Mar 2005 08:47:58 GMT

hi if you are using oracle database then try this

i am using emp table which has following table

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

select * from emp

where empno=(select empno from emp

having max(rownum)=(select count(rownum) from emp)

group by empno);

Rai Zeeshan Zaffar Khan.

Re: Finding last Record from a table

Former Member — Mon, 21 Mar 2005 16:17:55 GMT

Hi Charles,

I see what you mean. Yes, you are correct in saying that the last one entered need not be at the end of the table. I did a small test table with 3 key fields and one non-key field. Even though I entered records into the table in random order of the key fields, when I looked in SE16, they were all sorted by the key field. So that my first record entered ended up as the last record of the table(since its key was the highest for the sort). This is the behaviour that I wanted to present by saying 'automatically sorted', which is to indicate that no matter how you enter the records, system is going to sort the entries in ascending order of the table key, before saving them.

Sorry for the confusion.

Srinivas