topic Re: using max select query in Application Development and Automation Discussions

using max select query

Former Member — Fri, 20 Feb 2009 05:09:22 GMT

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.

Re: using max select query

I355602 — Fri, 20 Feb 2009 05:13:28 GMT

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

Re: using max select query

former_member222860 — Fri, 20 Feb 2009 05:24:48 GMT

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

Re: using max select query

Former Member — Fri, 20 Feb 2009 05:27:55 GMT

Hi:

add group by only

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

GROUP BY mjahr.

regards

Shashi

Re: using max select query

Former Member — Fri, 20 Feb 2009 05:45:53 GMT

hi,

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

Re: using max select query

former_member222860 — Fri, 20 Feb 2009 05:50:12 GMT

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

Re: using max select query

Former Member — Fri, 20 Feb 2009 06:58:53 GMT

Thanks mahesh and others.