Get last record of group of data with select statement

[Former Member]

Hi sapfans,

we have the requirement to get the last MBLNR per material no. of table mseg. Normally I would read all the data into an internal table and use a sort, a loop and at end of.. But we have 32 Mio data in table MSEG and I get a dump trying to read all data. Is there any additional option like distinct, max, group by of the select statement that I could use?

Every little hint is welcome!

Regards

Nicola

[Former Member]

data: w_mblnr like mseg-mblnr.

select max( mblnr ) from mseg

into w_mblnr

where........

w_mblnr will now have Maximum value, note that u have to give conditions accorrding to your requirement in WHERE clause

[kiran_k8]

Nicola,

Sort the internal table by descending so that you get the last record as the first record and then read the table with index so that you can get the first record.

In that way you will be getting the last record.

sort itab by matnr descending

read table itab index 1.

K.Kiran.

[Former Member]

Ok, but I have no where condition. I want all data of table mseg but only the last MBLNR per MATNR. How can I group the data within one select?

Kiran, as I wrote before - I can't read the data into an internal table.

Regards

Nicola

[kiran_k8]

Nicola,

Sorry boss for the wrong reply.

K.Kiran.

[Former Member]

Select max(mblnr) from matnr.

In the where condition give Matnr in s_Matnr. Declare s_matnr as select option and dont display it. It will fetch for all Matnr.

[Former Member]

Select * from mseg into table i_mblnr.

sort i_mblnr by matnr mseg descending.

Delete adjacent duplicates comparing matnr mseg.

after that you will get the last MBLNR, cause u want the last MBLNR right?

[Former Member]

tables mara.

data:

begin of IT_mseg OCCURS 0,

matnr like mara-matnr,

mblnr like mseg-mblnr,

end of IT_mseg,

begin of IT_MARA OCCURS 0,

matnr like mara-matnr,

end of IT_MARA.

select-options:

s_matnr for mara-matnr.

select matnr into table IT_MARA

from mara

where

matnr in s_matnr.

loop at IT_MARA.

select max( mblnr ) from mseg

into IT_MSEG-MBLNR

where

matnr eq IT_MARA-MATNR.

IF SY-SUBRC EQ 0.

IT_MSEG-MATNR = IT_MARA-MATNR.

APPEND IT_MSEG.

CLEAR IT_MSEG.

ENDIF.

endloop.

[Former Member]

tables mara.

data:

begin of IT_mseg OCCURS 0,

matnr like mara-matnr,

mblnr like mseg-mblnr,

end of IT_mseg,

begin of IT_MARA OCCURS 0,

matnr like mara-matnr,

end of IT_MARA.

select-options:

s_matnr for mara-matnr.

select matnr into table IT_MARA

from mara

where

matnr in s_matnr.

loop at IT_MARA.

select max( mblnr ) from mseg

into IT_MSEG-MBLNR

where

matnr eq IT_MARA-MATNR.

IF SY-SUBRC EQ 0.

IT_MSEG-MATNR = IT_MARA-MATNR.

APPEND IT_MSEG.

CLEAR IT_MSEG.

ENDIF.

endloop.

***IT_MSEG now contains MATNR with MAX MBLNR

[Former Member]

Please Sapfans - read my requirement carefully! I can't use internal tables due to storage problems. It will cause a dump. I'm looking for a solution within one select statement.

[dev_parbutteea]

Hi,

if i've well understood your requirements :

-you need to retrieve all material numbers with the their corresponding maximum value of mblnr.

types:begin of t_mseg,

matnr like mseg-matnr,

mblnr like mseg-mblnr,

end of t_mseg.

data:v_count like sy-tabix,

i_mseg type STANDARD TABLE OF t_mseg.

SELECT COUNT( DISTINCT matnr ) from mseg into v_count2.

SELECT matnr max( mblnr )

from mseg

INTO TABLE i_mseg

group by matnr.

sort i_mseg by matnr.

• Number of records in i_mseg shud equal v_count2

write : v_count2. " count2 = total number number of matnrs in mseg

Hope that this meets your requirements,

Revert back in case of doubts,

Regards,

Sooness.

[dev_parbutteea]

Hi,

small correction

<b>data:v_count2 like sy-tabix,

i_mseg type STANDARD TABLE OF t_mseg.</b>

Regards.

[kesavadas_thekkillath]

Try these...if useful apply...

1)

Sort itab by matnr mblnr descending.

loop at itab.

at new matnr.

continue

end at.

delete itab.

endloop.

2)

Sort itab by matnr mblnr descending.

loop at itab.

on change of itab-matnr.

move the record to other table.

endon

endloop.

[kesavadas_thekkillath]

once again kesav,

if there is large amount of data try using cursors.

or

Hope so before fetching from mseg u ll fetch mblnr from mkpf...if so..

select max( mblnr ) appending table itabmkpf from mkpf for the posting date(if any)

then use

SELECT mblnr matnr APPENDING TABLE itabmseg

FROM mseg CLIENT SPECIFIED FOR ALL ENTRIES IN itabmkpf

WHERE mandt = sy-mandt

AND mblnr = itabmkpf-mblnr

Try to reduce the query search including plants,date,bwart(if needed).

reward if useful

[Former Member]

Kesav,

I wrote that I can't use internal tables due to storage problems. So it's likely that a select statement with 'for all entries' wouldn't work as well. Even if I use a select max with table mkpf before, we'll still have millions of data.

It's a bit frustrating but I guess there is no solution for this requirement.

Regards

Nicola

[Former Member]

If u've a particular number range attached to ur documents then u can try to fetch the next number available using the FM <b>NUMBER_GET_NEXT</b>.

[former_member225631]

Declare one more internal table. Try as following.

loop at itab.

at end of matnr.

get the last record from itab.(work area), then display or add to second internal table..

endat.

endloop.

[Former Member]

Rajesh,

did you read the requirement?

[Former Member]

*CHECK THIS CODE, USES VERY LITTLE INTERNAL TABLE SPACE

REPORT ZTEST3.

tables mara.

data:

begin of IT_mseg OCCURS 0,

matnr like mara-matnr,

mblnr like mseg-mblnr,

end of IT_mseg,

W_MATNR LIKE MARA-MATNR.

select-options:

s_matnr for mara-matnr.

select matnr into W_MATNR

from mara

where

matnr in s_matnr.

select max( mblnr ) from mseg

into IT_MSEG-MBLNR

where

matnr eq W_MATNR.

IF SY-SUBRC EQ 0.

IT_MSEG-MATNR = W_MATNR.

APPEND IT_MSEG.

CLEAR IT_MSEG.

ENDIF.

ENDSELECT.

LOOP AT IT_MSEG.

WRITE:/ IT_MSEG-MATNR, IT_MSEG-MBLNR.

ENDLOOP.

[Former Member]

Tripat,

you've already posted this kind of solution and I've already rewarded points. We have nearly 2 Mio Data in Mara, so I can't use it but nevertheless it's the nearest solution.

Nicola

[dev_parbutteea]

Hi,

try this, copy and paste:

types:begin of t_mseg,

matnr like mseg-matnr,

mblnr like mseg-mblnr,

end of t_mseg.

data:v_count2 like sy-tabix,

i_mseg type STANDARD TABLE OF t_mseg.

SELECT COUNT( DISTINCT matnr ) from mseg into v_count2.

*you may remove the above statement- just for test

SELECT matnr max( mblnr )

from mseg

INTO TABLE i_mseg

group by matnr.

sort i_mseg by matnr.

• Number of records in i_mseg shud equal v_count2

write : v_count2. " count2 = total number number of matnrs in mseg

Hope that this meets your requirements,

Revert back in case of doubts,

Regards,

Sooness.

[Former Member]

Hi Sooness,

I don't understand the use of the select count..?

Anyway, I can't use the SELECT matnr max( mblnr ) because the highest number of mblnr must not be the last mblnr. It depends on the number range. There are material doc no. beginning with 49* and no. beginning with 5*

I'll have to add the creation date additionally to the select that is within one select impossible.

Thanks to all who tried to solve this problem for me.

Regards

Nicola

[Former Member]

if the date is the criteria then you can write:

REPORT ZTEST3.

tables mara.

data:

begin of IT_mseg OCCURS 0,

matnr like mara-matnr,

mblnr like mseg-mblnr,

DATE LIKE .........

end of IT_mseg,

W_MATNR LIKE MARA-MATNR.

select-options:

s_matnr for mara-matnr.

select matnr into W_MATNR

from mara

where

matnr in s_matnr.

select mblnr max( date ) from mseg

into (IT_MSEG-MBLNR,IT_MSEG-DATE)

where

matnr eq W_MATNR.

IF SY-SUBRC EQ 0.

IT_MSEG-MATNR = W_MATNR.

APPEND IT_MSEG.

CLEAR IT_MSEG.

ENDIF.

ENDSELECT.

LOOP AT IT_MSEG.

WRITE:/ IT_MSEG-MATNR, IT_MSEG-MBLNR,IT_MSEG-DATE.

ENDLOOP.

[Former Member]

Hi,

Use this code to get last record.

data: mblnr like mseg-mblnr.

select max( mblnr ) from mseg into mblnr.

IF USEFULL REWARD