2nd Maximum Record

[Former Member]

Hi Experts,

I am new to ABAP. I want to generate a report wherein I have to display 2nd largest record from an internal table. If I sort this table in descending and delete adjacent duplicates, I will only get 1st record. What is the procedure to capture 2nd record. If there is no 2nd largest in internal table, 1st record should be captured. There are many data combinations in this internal table. Kindly provide a logic to fetch the records.

Regards,

Sam

[venkateswaran_k]

Hi

Assume you use SQL Server.

SELECT TOP 2 <columns> FROM <Table>

Regards.

Venkat

[Former Member]

I am fetching data from 2 tables using inner join. Now I want to display 2nd data from this internal table. My comparison will be based on material number, po number and po date.

Regards,

Sam

[venkateswaran_k]

You need to add one more field in your Internal table as  SortId.

SORT <interna table> by Matnr, ponumber, date

LOOP at <internal table>

   Based on the group update sort id as 1, 2 , 3 etc0

ENDLOOP

ten select the record from that internal tbale with sortid as 2

[former_member213851]

Hi Sam,

sort table first based on primary keys in  descending order and then delete first record to get Second maximum record.

[Former Member]

I want to delete data which is repeating and maximum. If there is only one data with the above combination(Material Number, PO Number and Po Date) it should not be deleted.

Eg:

Material No     PO#                      PO Date

100001           4500000010         01.01.2012

100001           4500000009         31.12.2011

100001           4500000007         30.12.2011

100002           4500000005         28.12.2011

My output should be as follows:

Material No     PO#                     PO Date

100001           4500000009        31.12.2011

100002           4500000005        28.12.2011

Regards,

Sam

[former_member213851]

Hi Sam,

Please folloe below steps:

1. sort internal table based on primary keys Material No  , PO#    and PO Date.

2  loop at gt_tab into wa_tab.

        exit.

    endloop

3.  delete 1 st record from gt_tab

4 check if gt_tab is initial or not.

     if yes, dipaly wa_tab  contents

   else.

   dispaly second max record

Regards,

Sachin

[Former Member]

Hi Sachin,

As per your logic only one record from internal table will be displayed. I want to display 2nd highest data based on material number.

Regards,

Sam

[Former Member]

Use this. I couldn't test this. Let me know if you face any issues.

DATA : lv_new,
           lv_last,
           lv_write.
SORT table.
LOOP AT table INTO wa.
CLEAR: lv_new, lv_last.
AT NEW matnr.
lv_new = 'X'.
CLEAR lv_write.
ENDAT.
AT ENDOF matnr.
lv_last = 'X'.
ENDAT.
IF lv_new = 'X' AND lv_last = 'X'. " Only one line for material
   WRITE : wa-matnr. " write contents
   lv_write = 'X'.
ELSE IF lv_new = 'X' AND lv_last IS INITIAL.
   CONTINUE.
ELSE IF lv_new IS INITIAL AND lv_last IS INITIAL.
 IF lv_write IS INITIAL.
  WRITE : wa-matnr. " write contents
  lv_write = 'X'.
 ENDIF.
ELSE IF lv_last = 'X'.
   IF lv_write IS INITIAL.
   WRITE: wa-matnr. " write contents
   lv_write = 'X'.
 ENDIF.
ENDIF.
ENDLOOP.

Thanks,

Shambu

[Former Member]

Thanks a lot for your inputs.

Regards,

Sam

[former_member209920]

Hi Sam,

This is one of the most common interview question

Regrds