topic 2nd Maximum Record in Application Development and Automation Discussions

2nd Maximum Record

Former Member — Sat, 02 Jun 2012 13:30:05 GMT

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

Re: 2nd Maximum Record

venkateswaran_k — Sat, 02 Jun 2012 13:39:46 GMT

Assume you use SQL Server.

SELECT TOP 2 <columns> FROM <Table>

Regards.

Venkat

Re: 2nd Maximum Record

former_member213851 — Sat, 02 Jun 2012 13:43:07 GMT

Hi Sam,

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

Re: 2nd Maximum Record

Former Member — Sat, 02 Jun 2012 13:45:16 GMT

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

Re: 2nd Maximum Record

venkateswaran_k — Sat, 02 Jun 2012 13:53:26 GMT

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

Re: 2nd Maximum Record

Former Member — Sat, 02 Jun 2012 13:54:02 GMT

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

Re: 2nd Maximum Record

former_member213851 — Sat, 02 Jun 2012 13:58:48 GMT

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

Re: 2nd Maximum Record

Former Member — Sun, 03 Jun 2012 05:00:04 GMT

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

Re: 2nd Maximum Record

Former Member — Sun, 03 Jun 2012 10:45:26 GMT

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

Re: 2nd Maximum Record

Former Member — Sun, 03 Jun 2012 11:46:42 GMT

Thanks a lot for your inputs.

Regards,

Sam

Re: 2nd Maximum Record

former_member209920 — Mon, 04 Jun 2012 05:21:13 GMT

Hi Sam,

This is one of the most common interview question

Regrds