MAX( ) function in 2 columns... Wrong Data

[Former Member]

Hi Folks,

I have A DDIC Table with

mandt, Destination, Date, Time, KUNNR and some other columns.

Now I want to determine the LATEST entry for a KUNNR.

Now I did:

SELECT

mandt desti kunnr MAX( erdat )

MAX( ertim ) [..other nonkey fields.]

FROM MYTABLE INTO wa

WHERE desti = '10010'

AND kunnr = '0000000005'

AND not_checked = 'X'

GROUP BY mandt desti kunnr [..other nonkey fields.].

WRITE [ALL FIELDS]

ENDSELECT.

Now I get 5(!) results:

001 RE3_001 5 11.03.2009 12:59:55

001 RE3_001 5 12.03.2009 15:29:01

001 RE3_001 5 11.03.2009 19:27:50

001 RE3_001 5 11.03.2009 13:23:30

001 RE3_001 5 30.06.2008 16:45:26

what is that?

I only needed the 12.03.2009 at 15:29:01 .....

what do I do wrong?

what about the MAX() ?

THX

[Former Member]

Hello,

Fetch all the data from the MYTABLE with where conditions.

Then sort the table with Date and time key with descending order after sorting read the MyTable with index 1.

You will get it..............

[Former Member]

k.. thx...

but WHY do I get the 11.03 at all?

or the 30.08? there is no MAX at all in this column ?

I thought the MAX means 1 line ... ONLY if I have 2 equal lines, Ill get 2 ... but 5lines, which are not equal at date and time? that sounds weird...

[Former Member]

Hi Markus,

As a alternate you can select all & sort it and delete it by keeping the first record.. this is the simplest way as both has the same effect on the database,

Thanks & regards,

Dileep .C

[rainer_hbenthal]

> SELECT
>        mandt desti kunnr MAX( erdat )
>        MAX( ertim ) 
>        FROM MYTABLE INTO wa
>        WHERE desti = '10010'
>        AND   kunnr = '0000000005'
>        AND   not_checked = 'X'
>        GROUP BY mandt desti kunnr .
> 
> ENDSELECT.
> THX

Try something like this:

SELECT
  desti kunnr
FROM MYTABLE as o INTO wa
WHERE desti = '10010'
AND   kunnr = '0000000005'
AND   not_checked = 'X'
AND ertim = ( select max(ertim)
                     from mytable as i
                     where o~desti = i~desti
                       and o~kunnr = i~kunnr )
ENDSELECT.

Maybe you have to adopt the select statement above to fit your needs a little bit.

[Former Member]

I cant use any subquery. its a table with over 1.000.000 entries....

And: my Problem is NOT how I get the Data, I want to know WHY the MAX() doesnt work as expected...

If I delete all other fields except ERDAT and ERTIM

I get the maximum Date (15.03) and the maximum time (19:XX) ...

why???

Edited by: Markus Völker on Apr 22, 2009 10:43 AM

[rainer_hbenthal]

A group by clause used for a big table is even more worth as a subselect with an index an the join condition.

The group by is just not working as it is expected by you. you can make it working using the having clause but at least this will lead to a subquery again. The problem is not the max clause, its the group by clause.

BTW: if you want to know why your select statement does not work, why are you asking in a forum where the topic is abap?

[Former Member]

Hi Markus,

The MAX field will select the Maximum no.

where as it doesnt recognise date fields ithink so.

Since the todays date is stored as 22042009 and lets say last year 22122008... now it picks the december value as the MAX value..

and its doesnt take the combination... you cannot use 2 MAX for one select Query,,

if you use there will be same type of miss match as it doest compare the Time for the particular day instead it looks for all the data...

I think so this is the problem,,,

Thanks & regards,

Dileep .C

[Former Member]

thx..

I asked here, because it is ABAP.

Where else should I have asked?

Edited by: Markus Völker on Apr 22, 2009 11:01 AM