Select Query

[Former Member]

How will write a select query where a date field should be maximum out of all values present for that field?

[Former Member]

hi yogesh,

Use the aggregate MAX addition with date field.

MAX( [DISTINCT] col ) - Determines the maximum value of the value in the column col in the resulting set or in the current group.

Example

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.

Best of luck,

Bhumika

[bpawanchand]

hi

use aggregate function MAX on that praticular date field

sample snippet

SELECT

MAX( erdat )

INTO ( w_d )

FROM mara.

ENDSELECT.

observe the MAX aggregate funtion

regards

pavan

[Former Member]

hi yogesh,

Use the aggregate MAX addition with date field.

MAX( [DISTINCT] col ) - Determines the maximum value of the value in the column col in the resulting set or in the current group.

Example

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.

Best of luck,

Bhumika

[Former Member]

Hi,

Use the 'MAX' function.

select MAX ( <fieldname> )

from <tablename>

into <variablename>

based on the conditions.

The above function selects the maximum value of the given field from the table.

Reward if helpful,

Sharin.