select maximum

[Former Member]

hi experts,

SELECT PSPNR

REVISION

DU

BOM_NO

ITEM_NO

UNIT

MATNR

FROM ZMED_GMSDATA INTO TABLE IT_GMS

FOR ALL ENTRIES IN IT_FINAL WHERE pspnr = it_final-pspnr AND DU = IT_FINAL-ZZDU.

here i want to select maximum of DU please help

[Former Member]

Hi rajashiva

use this code

SELECT PSPNR
REVISION

 MAX( DU )

BOM_NO
ITEM_NO
UNIT
MATNR
FROM ZMED_GMSDATA INTO TABLE IT_GMS
FOR ALL ENTRIES IN IT_FINAL WHERE pspnr = it_final-pspnr AND DU = IT_FINAL-ZZDU

best regards

Marco

[SuhaSaha]

Hi Marco,

Unfortunately you cannot use any aggregate functions with FAE except COUNT (*). SAP says:

If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used

@OP: You have to sort your internal table with DU as descending & then DELETE ADJACENT DUPLICATES.

BR,

Suhas

PS: While using FAE always remember to select all the key fields .

[gastn_jareo]

I don't understand. You want the maximum DU, but you have the clause DU = IT_FINAL-ZZDU.

You want maximum DU in wich group of data? Or you want to use maximum DU in IT_FINAL for each IT_FINAL-POSNR? If this is the case you shoud delete previously records on IT_FINAL wich DU are not the maximum for each POSNR.

But please, clarify your request.

Regards!

[gastn_jareo]

If you want to select only one record, and that record is the one that has the maximum value of DU then you should sort IT_FINAL by DU descending (as Suhas said) and then select data with this clause:

FROM ZMED_GMSDATA
     UP TO 1 ROWS
     INTO TABLE IT_GMS

Furthermore, as it will give you only one record you can use a structure instead an internal table to store the result.

Regards!