max

[Former Member]

how to use max function

[Former Member]

Hi

MAX( [DISTINCT] fdescriptor ).

Effect

For the lines selected, returns the largest value in the column specified by the field label fdescriptor. The DISTINCT declaration does not affect the result. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the highest ticket price for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

max TYPE sbook-loccuram.

SELECT scustom_{name scustom}postcode scustom~city

MAX( sbook~loccuram )

INTO (name, postcode, city, max)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, max.

ENDSELECT.

Regards

Ravish Garg

<i>

Reward if useful</i><b></b>

[Former Member]

Example:

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

[Former Member]

Hi,

For Eg.

SELECT MAX( fld1 ) FROM <table> INTO <var>.

It will return the Maximum Value of column <fld1> into the Variable <var>.

Regards,

Padmam.

[Former Member]

hi

good

The ALL function is a set function that removes the NULL values.

Syntax

<all_function>::= <set_function_name> ( [ALL] <expression> )

http://www.sapdb.org/7.4/htmhelp/48/b4e43dc0ca11d2a97100a0c9449261/content.htm

thanks

mrutyun^

[Former Member]

Hi

Check below eg:

TABLES: SCUSTOM, SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

MAX( SBOOK~LOCCURAM )

INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM)

FROM SCUSTOM INNER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID

WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND

SBOOK~CARRID = 'LH ' AND

SBOOK~CONNID = '0400'

GROUP BY SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

ORDER BY SCUSTOM~NAME.

WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-LOCCURAM.

ENDSELECT.

Hope this helps .

Praveen

[Former Member]

Hi Pandu,

Min (minimum value of the column ) , max( maximum value of the column )

average (average of all the values , but this will ignore the null values ), sum (count of all values in the column) .

Avoid using average when there are more null values , for others its not an issue .

Regards,

Gajalakshmi .

[Former Member]

Hi Pandu,

MAX: returns the maximum value of the column

Try This Example

DATA: CARRID TYPE SFLIGHT-CARRID,

MINIMUM TYPE P DECIMALS 2,

MAXIMUM TYPE P DECIMALS 2.

SELECT CARRID MIN( PRICE ) MAX( PRICE )

INTO (CARRID, MINIMUM, MAXIMUM)

FROM SFLIGHT

GROUP BY CARRID.

WRITE: / CARRID, MINIMUM, MAXIMUM.

ENDSELECT.

<b>Reward All Helpful Answers</b>

Regards,

Murali