Need help with sql stmt

[Former Member]

I have select stmt like this, but this is not returning the max value of the field f2. Just gives me 0 even though there are 2 entries in the table.

select f1 MAX( f2 ) from <table>

into corresponding fields of table <itab>

where f1 in r_f1

group by f1.

What am I missing? I appreciate your help.

Thanks

Anu

[Former Member]

did you declare f2 like <table>-f2 in your <itab> struct.

Below statement is working perfectly all right for me.

SELECT MAX( saptdid ) INTO wa_header-saptdid FROM zfi_ephdr.

[Former Member]

yes I did Amit . But I need the key field(f1) and its max value for f2, not the max of f2 from the whole table.

Each f1 starts from 1 and gets incremented like internal number concept.

Whatever you said works good. But that is not what I need.

Thanks

Anu

[Former Member]

Hi Anupama,

Use some variables like this.

data: f1 type table-field_name,

maximum type i.

Select f1 MAX( f2 ) into (variable_name1, maximum) from <table> group by field_name.

Here key field can be f1.

This will work.

Regards,

SP.

[Former Member]

I am not sure an aggregate function will give the same name as the field... meaning, MAX( f2 ) does not produce a field called f2. I could be wrong.

Are there only two fields in <itab>? If yes, remove "corresponding fields".

You could do us an alias like "MAX( f2 ) as f2".

[Former Member]

How about using below logic instead of your logic.

select f1 MAX( f2 ) from <table>

into corresponding fields of table <itab>

where f1 in r_f1.

I am not sure about group by here

How about using below logic instead of your logic.

select f1 f2 from <table>

into corresponding fields of table <itab>

where f1 in r_f1.

then sort <itab> descending.

then Read the record from the internal table passing the appropriate key.

Hope this will work

Thanks,

amit

[Former Member]

I appreciate all your prompt responses. I wanted to get in a single select hit for a range of keyfields. If it was one, then I wouldn't have a problem. But anyway

I have

select f1 max( f2 ) as f2

from <table>

into corresponding fields of <itab>

where f1 in r_f1

group by f1.

This works perfect. Rewarded points.

Thanks

Anu

[Former Member]

Small correction.

select f1 max( <b>Distinct</b> f2 ) as f2

from <table>

into corresponding fields of <itab>

where f1 in r_f1

group by f1.

Thanks

Anu

[Former Member]

hi anupama,

give like this....

select f1 MAX( f2 ) from <table>

into corresponding fields of table <itab>

where f1 in r_f1

group by f1 f2

order by f1.

and check this also ....its from help

MAX( [DISTINCT] fdescriptor )

Addition:

... AS alias

Effect

Returns the largest value for the selected lines in thecolumn identified by the Field descriptor fdescriptor.The DISTINCT specification does not alter theresult. NULL values are ignored in the calculation, except whenall of the values in a column are NULL. In this case, the resultis NULL.

Example

Output a list of all customers on Lufthansa flight 0400in 1995, along with the highest price paid, sorted by customer name:

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.

[Former Member]

Hi ,

A small correction. I think this would work fine...

Data: var1 type table-f1,

maxi type i.

select f1 MAX( f2 )into (var1, maxi) from <table> group by f1.

Key field is f1.

Regards,

SP.