select max problem

[Former Member]

Hi experts

I have this long query:

SELECT DISTINCT MAX( vbka~ktabg ) "add

mseg~mblnr

mseg~mjahr

mseg~ebeln

mseg~lifnr

mseg~matnr

mseg~grund

mseg~erfmg

mseg~erfme

mseg~shkzg

knvv~kvgr1

tvv1t~bezei

lfa1~name1

lfa1~stras

lfa1~ort01

lfa1~telf1

lfa1~kunnr

eipa~preis

eipa~bwaer

eipa~peinh

eipa~bprme

FROM mseg JOIN mkpf ON mseg_{mblnr = mkpf}mblnr

AND mseg_{mjahr = mkpf}mjahr

JOIN ekpo ON mseg_{ebeln = ekpo}ebeln

JOIN eine ON ekpo_{infnr = eine}infnr

JOIN eipa ON eine_{ebeln = eipa}ebeln

AND eine_{ebelp = eipa}ebelp

JOIN lfa1 ON mseg_{lifnr = lfa1}lifnr

JOIN knvv ON lfa1_{kunnr = knvv}kunnr

JOIN tvv1t ON knvv_{kvgr1 = tvv1t}kvgr1

JOIN vbka ON lfa1_{kunnr = vbka}kunnr "add

into CORRESPONDING FIELDS OF TABLE i_data1

WHERE mseg~lifnr IN r_lifnr

AND mseg~matnr = 'B1992'

AND ( mseg_{bwart = '101' or mseg}bwart = '102' )

AND mseg~grund IN r_grund

AND mkpf~budat IN r_budat

AND tvv1t~spras = 'B'

AND eine~esokz = '0'

and vbka~ktaar = '0010' "add

group by

mseg~mblnr

mseg~mjahr

mseg~ebeln

mseg~lifnr

mseg~matnr

mseg~grund

mseg~erfmg

mseg~erfme

mseg~shkzg

knvv~kvgr1

tvv1t~bezei

lfa1~name1

lfa1~stras

lfa1~ort01

lfa1~telf1

lfa1~kunnr

eipa~preis

eipa~bwaer

eipa~peinh

eipa~bprme.

I get results, but in the field vbka~ktabg i get 00000000 although i know that there is values there.

What's wrong here, and how can i do this right.

Thanks

Amit

[christine_evans]

You don't need to use DISTINCT and GROUP BY and this may be what is causing the problem. Try with GROUP BY only.