topic Re: Select Distinct/MAX in Application Development and Automation Discussions

Select Distinct/MAX

former_member447950 — Wed, 02 Dec 2015 21:48:21 GMT

Hello,

I have the following entries in MARC (example)

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 — Wed, 02 Dec 2015 22:41:56 GMT

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)

VenkatRamesh_V — Thu, 03 Dec 2015 01:50:26 GMT

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.

RaymondGiuseppi — Thu, 03 Dec 2015 08:49:58 GMT

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

roberto_forti — Thu, 03 Dec 2015 11:26:34 GMT

Hi Jess,

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

Regards