using max select query

[Former Member]

Hi all,

LOOP AT ITAB.

IF S_KUNNR IS not INITIAL.

SELECT MAX( MBLNR ) FROM MSEG INTO MBLNR

WHERE MATNR EQ ITAB-MATNR

AND KUNNR EQ ITAB-KUNNR

AND BWART eq '951'.

else.

SELECT MAX( MBLNR ) FROM MSEG INTO MBLNR

WHERE MATNR EQ ITAB-MATNR

AND KUNNR ne ' '

and sobkz eq 'V'

AND BWART eq '951'.

endif.

i need mjahr also from mseg .Top part is working.

********************************************************************************

LOOP AT ITAB.

IF S_KUNNR IS not INITIAL.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO MBLNR mjahr

WHERE MATNR EQ ITAB-MATNR

AND KUNNR EQ ITAB-KUNNR

AND BWART eq '951'.

else.

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO MBLNR mjahr

WHERE MATNR EQ ITAB-MATNR

AND KUNNR ne ' '

and sobkz eq 'V'

AND BWART eq '951'.

endif.

**********************************************************************************

after adding mjahr its not working.how to write query for that.

[I355602]

Hi,

We cant fetch any other data if using the aggregate function in query.

First you need to find the max value (MBLNR) and then based on the combination of data you need to find the field MJAHR.

Hope this helps you.

Regards,

Tarun

[former_member222860]

Hi,

Check this:

tables: mseg.
data: mblnr like mseg-mblnr,
      mjahr like mseg-mjahr.

SELECT MAX( MBLNR ) mseg~mjahr FROM MSEG INTO (MBLNR, mjahr)
GROUP BY mseg~mjahr.

WRITE:/ MBLNR, MJAHR.
ENDSELECT.

thanks\

Mahesh

[Former Member]

Hi:

add group by only

like

SELECT MAX( MBLNR ) mjahr FROM MSEG INTO (MBLNR, mjahr)

GROUP BY mjahr.

regards

Shashi

[Former Member]

hi,

I could not able to use where condition in group by.How to use where condition in that.

[former_member222860]

Like this:

tables: mseg.
data: mblnr like mseg-mblnr,
      mjahr like mseg-mjahr.

parameter: p_matnr like mseg-matnr.

SELECT MAX( MBLNR ) mseg~mjahr FROM MSEG INTO (MBLNR, mjahr)
where matnr eq p_matnr
GROUP BY mseg~mjahr.

WRITE:/ MBLNR, MJAHR.
ENDSELECT.

Pl. see ur other thread on the same and close either of them.

thanks\

Mahesh

[Former Member]

Thanks mahesh and others.