topic Re: max in Application Development and Automation Discussions

max

Former Member — Mon, 20 Aug 2007 08:52:45 GMT

how to use max function

Re: max

Former Member — Mon, 20 Aug 2007 08:55:14 GMT

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

Reward if useful

Re: max

Former Member — Mon, 20 Aug 2007 08:56:58 GMT

Example:

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Re: max

Former Member — Mon, 20 Aug 2007 08:58:21 GMT

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.

Re: max

Former Member — Mon, 20 Aug 2007 09:06:37 GMT

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^

Re: max

Former Member — Mon, 20 Aug 2007 09:16:32 GMT

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

Re: max

Former Member — Mon, 20 Aug 2007 09:41:26 GMT

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 .

Re: max

Former Member — Mon, 20 Aug 2007 10:37:16 GMT

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.

Reward All Helpful Answers

Regards,

Murali