Skip to Content

How can I select two records based upon the latest BUDAT in MSEG

Sep 12, 2017 at 12:11 PM


avatar image
Former Member

Hi Experts.

I got a set of article in my internal table gt_mara. I want to select latest two records of data from MSEG table based upon BUDAT ( entry date) for all the entries of GT_mara with good performance.

I can use MAX function . But will return single record. But i need two.,


shefeek m k

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Sandra Rossi Sep 13, 2017 at 01:26 PM

I guess there's no simple solution. Either use a big SELECT and remove the entries from the internal table (see Horst answer), or if you have a small volume see Bertrand answer), or use native SQL (you'll have to get rid of FOR ALL ENTRIES too; for Oracle, use RANK you may have a look here: ).

10 |10000 characters needed characters left characters exceeded
Horst Keller
Sep 12, 2017 at 03:23 PM

Enhance your internal table with an appropriate (secondary) sorted key and get the two lines using the (secondary) table index.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

How would OP get two lines with latest dates using a table index? This is a descending sort situation but the keys are in ascending order, I believe. At least I don't recall a different option in ECC 6.0... So index or not OP would still end up reading all records (per MATNR and whatever other criteria can be used) and then going through them searching for 2 latest dates, right?


There are so many ways. ... INDEX lines( itab ) - 1, INDEX lines( itab ), grouping, temporary sorted subtables ...


Ah, OK, I thought you meant an internal table and a database table index. But after re-reading the answer again I see it was all just about an internal table. Nevermind then. Thank you!

Former Member

Thanks for your answer.. Still I am confused, how to do.. Let me provide my code, how I handled.. Pls check and give me more performance way. Expecting GT_mara is 15000 records and each material in mseg got 20000 records..

--->selecting the mseg data first.

SELECT matnr bwart werks lgort umwrk shkzg budat_mkpf ebeln lifnr


WHERE matnr EQ gt_mara-matnr AND werks IN so_werks AND

bwart EQ '101' AND lifnr NE space .

----->selecting the latest two records for comparing both. if its same first record is updated as 'SAME',

-----> if its different updating it as 'DIFF'. at last keeping the records which got 'SAME'. deleting remaining all.

SORT gt_mseg DESCENDING BY matnr budat .

clear lwa_mseg.

LOOP AT gt_mseg INTO gwa_mseg.

lv_tabix = sy-tabix.

lwa_mseg_tmp = gwa_mseg.

AT NEW matnr.

lwa_mseg = lwa_mseg_tmp.

lv_count = 1.

gwa_mseg-compr = 'SAME'.

MODIFY gt_mseg FROM gwa_mseg INDEX lv_tabix TRANSPORTING compr.


if gwa_mseg-matnr = lwa_mseg-matnr.

if lv_count = 2.

if lwa_mseg-umwrk = gwa_mseg-umwrk.

clear lv_count.



gwa_mseg-compr = 'DIFF'.

lv_tabix = lv_tabix - 1.

modify gt_mseg FROM gwa_mseg INDEX lv_tabix TRANSPORTING compr.

clear lv_count.




IF lv_count = 1.

lv_count = 2.




DELETE gt_mseg WHERE compr <> 'SAME'.

Bertrand DELVALLEE Sep 12, 2017 at 03:52 PM


It depends mostly of numbers of entries involved.

What is the size of gt_mara?

How many entries expected in MSEG for 1 entry in gt_mara?

If these 2 numbers are relatively small you can do a LOOP on gt_mara and SELECT (needed_fields) from MSEG where (cond) ORDER BY BUDAT descendig UP TO 2 ENTRIES. If your condition fill (with operator EQ) all keys of a MSEG activated index it will be as fast as possible. If this index doesn't exist you can create it in SE11.

Best regards


Show 2 Share
10 |10000 characters needed characters left characters exceeded

SELECT in the LOOP? That's some sort of ABAP blasphemy...


If you only pick 4 words in my sentences you can also blame for writing "LOOP descending create index". Which doesn't make more sense. :)