topic Re: max aggregate functions in Application Development and Automation Discussions

max aggregate functions

sudhakar196 — Wed, 25 Jul 2007 03:47:04 GMT

Hi,

in VBAK table i need latest date entered i.e max date value, field is ERDAT

can anybody write syntax for this

select max( ERDAT )

from vbak

into maxfeild

group by ERDAT.

endselect.

i think it is wrong can anybody write correct syntax.

Re: max aggregate functions

Former Member — Wed, 25 Jul 2007 03:51:48 GMT

Try using this code.

IF NOT it_ekpo[] IS INITIAL.

SELECT ebeln

ebelp

etenr

eindt

menge

wemng

FROM eket

INTO TABLE it_eket

FOR ALL ENTRIES IN it_ekpo

WHERE ebeln EQ it_ekpo-ebeln

AND ebelp EQ it_ekpo-ebelp.

IF sy-subrc EQ 0.

SORT it_eket BY ebeln ASCENDING

ebelp ASCENDING

eindt DESCENDING.

DELETE ADJACENT DUPLICATES FROM it_eket COMPARING ebeln

ebelp.

ENDIF.

check the below link :

Thanks

Seshu

Message was edited by:

Seshu Maramreddy

Re: max aggregate functions

Former Member — Wed, 25 Jul 2007 03:59:42 GMT

Hi Sudhakar,

Check this syntax

select max( ERDAT )

from vbak

into maxfeild.

<b>*group by ERDAT.

*endselect.

</b>

1) If you specify aggregate functions together with one or more database fields in a SELECT clause, all database fields not used in one of the aggregate functions must be listed in the GROUP-BY clause . Here, the result of the selection is a table.

2) If only aggregate functions occur in the SELECT clause, the result of the selection is a single record. Here, the SELECT command is not followed later by an ENDSELECT .

Thanks,

Vinay

Re: max aggregate functions

Former Member — Wed, 25 Jul 2007 04:06:07 GMT

This is the correct one

data maxfield type vbak-erdat .

select max( ERDAT )

from vbak

into maxfield.

Reawrd if useful

Re: max aggregate functions

Former Member — Wed, 25 Jul 2007 04:06:16 GMT

select max( ERDAT )

into maxfeild

from vbak.

regards

shiba dutta

Re: max aggregate functions

kesavadas_thekkillath — Wed, 25 Jul 2007 04:48:16 GMT

if u only want the max date entered.

try this,

select single max ( field ) into <table> from <database> up to 1 rows.

if there is chances of so many entries for the maximum date.

try this

select kunnr max ( field ) into <table> from <database>

reward if usefull

Re: max aggregate functions

kesavadas_thekkillath — Wed, 25 Jul 2007 04:49:58 GMT

or u can use this too..

select single date from vbak into <itab> order by date descending.

but using order by is a bit worst than using max.

try using max for performance

Re: max aggregate functions

macristina_briceo — Fri, 09 Jul 2010 20:17:51 GMT

hey,

as Seshu Maramreddy said, thats the best way.

First u should fill the itab, then sort it and after