Skip to Content
author's profile photo Former Member
Former Member

Get last record of group of data with select statement

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

14 Answers

  • Best Answer
    Posted on Jul 06, 2007 at 04:09 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 06, 2007 at 05:18 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 01:00 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 01:07 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 01:08 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 06, 2007 at 05:24 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 12:54 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 05, 2007 at 12:56 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 01:05 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2007 at 01:09 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.