Select Distinct/MAX

[former_member447950]

Hello,

I have the following entries in MARC (example)

Material	Plant	Profit Center
123456	US	500000
123456	MX	500000
123456	TJ	600000
123456	AU	500000
123456	FR	500000
7890	US	600000
7890	MX	600000
7890	AU	500000
7890	FR	600000

I would like to do something like the following:

SELECT DISTINCT on material number with the MAXIMUM number of profit centers

So I would end up with the following entries in my internal table:

123456  500000

7890     600000

Unfortunately, I cannot restrict based on plant because there isn't one plant that all Materials exist in.

I understand I can select all records and delete duplicates but this does not guarantee that I will get the profit center which appears the most.

Thank you!

[former_member183045]

I would suggest first to make an ordered group by:

SELECT material, profit_center, count( * )  as amount

FROM MARC

INTO CORRESPONDING FIELDS OF lv_table

GROUP BY material, profit_center

ORDER BY material, amount descending.

Then loop through the result table lv_table and write the first element of each material (eg. by writing them into a new table)

[RaymondGiuseppi]

Not DISTINCT but GROUP BY, did you try a simple:

SELECT matnr MAX( prctr ) INTO CORRESPONDING FIELDS OF marc FROM marc GROUP BY matnr.

Hint: Read online documention of SELECT statement for aggregate.

Regards,

Raymond

[VenkatRamesh_V]

Hi  jess,

Try,

Sort  internal table by material profit center descending.

using the read statement with  material, you can get desired result.

Hope it helpful,

Regards,

Venkat.

[roberto_forti]

Hi Jess,

About performance you can sort by material profit descending and read with material to get the first material record .

Regards