topic Re: Select staement with max in Application Development and Automation Discussions

Select staement with max

Former Member — Wed, 16 May 2007 20:09:01 GMT

Hi all ,

This is my select statement i want to select max(udate and utime) .How can i write this statement .

SELECT objectid username max( udate) FROM cdhdr INTO TABLE t_cdhdr FOR ALL ENTRIES IN t_cdpos WHERE objectclas ='MATERIAL' AND

objectid = t_cdpos-objectid AND changenr = t_cdpos-changenr.

Can any one guide me to resolve this issue?

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:13:05 GMT

Hi,

You can not use 'FOR ALL ENTRIES' for aggregation and only work for count ( * ).

Please try this.


SELECT objectid username udate 
FROM cdhdr 
INTO TABLE t_cdhdr 
FOR ALL ENTRIES IN t_cdpos 
WHERE objectclas ='MATERIAL' 
  AND objectid = t_cdpos-objectid 
  AND changenr = t_cdpos-changenr.

SORT t_cdhdr by udate descending.

READ table t_cdhdr index 1.

...

OR

SELECT objectid username udate 
FROM cdhdr 
INTO TABLE t_cdhdr 
FOR ALL ENTRIES IN t_cdpos 
WHERE objectclas ='MATERIAL' 
  AND objectid = t_cdpos-objectid 
  AND changenr = t_cdpos-changenr
  AND udate in   
( SELECT MAX( udate )
  FROM cdhdr
  WHERE objectclas ='MATERIAL'
    AND objectid = t_cdpos-objectid 
    AND changenr = t_cdpos-changenr ).

Regards,

Ferry Lianto

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:13:47 GMT

Hi Priya,

SELECT objectid username max ( udate ) max ( utime ) FROM cdhdr INTO TABLE t_cdhdr FOR ALL ENTRIES IN t_cdpos WHERE objectclas ='MATERIAL' AND objectid = t_cdpos-objectid AND changenr = t_cdpos-changenr.

SELECT objectid username udate utime FROM cdhdr INTO TABLE t_cdhdr FOR ALL ENTRIES IN t_cdpos WHERE objectclas ='MATERIAL' AND objectid = t_cdpos-objectid AND changenr = t_cdpos-changenr.

Sort itab descending by udate utime.

delete itab index NE 1.

Regards

SAB

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:14:31 GMT

Hi Ferry ,

In my select statement i am using FOR ALL ENTRIES. Is For all entries works for aggrate functions?

I need to select max ( udate and utime ) . How can i select max of these two fields.?

null

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:16:06 GMT

It should work just as you have it there. Just make sure there is a space on either side of udate within the parentheses. To get max of utime, just sort your table by utime descending and take the first record.

- April King

Message was edited by:

April King

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:16:30 GMT

You can try by selecting all the records, Then sort the table and then delete the rest else the MAX.

Shreekant

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:30:57 GMT

You have to use GROUP BY objectid username in order to achieve this as shown below.


SELECT objectid username MAX( udate ) MAX( utime )
  FROM cdhdr INTO TABLE t_cdhdr
 WHERE objectclas ='MATERIAL' GROUP BY objectid username.

But the problem here is that once you use the username in the GROUP BY clause, you will get the MAX values for that objectid and that user name not across the board.

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:33:02 GMT

Hi,

I would say get the information for namelastnamemax(date) into internal table itab and use the below after that.

SELECT * into table itab1

from ztable

for all enteries in itab

where name = itab-name

and lastname = itab-lastname

and date = itab-date.

Regards,

Bhaskar

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:36:37 GMT

Use code similar to the following.

  DATA t_cdpos_tmp LIKE TABLE OF t_cdpos.

  IF NOT t_cdpos[] IS INITIAL.
    t_cdpos_tmp[] = t_cdpos[].
    SORT t_cdpos_tmp BY objectid
                        changenr.
    DELETE ADJACENT DUPLICATES FROM t_cdpos_tmp
      COMPARING  objectid
                 changenr.
    SELECT objectid
           changenr
           username
           udate
      FROM cdhdr
      INTO TABLE t_cdhdr
      FOR ALL ENTRIES IN t_cdpos_tmp
      WHERE objectclas EQ 'MATERIAL'
      AND   objectid   EQ t_cdpos_tmp-objectid
      AND   changenr   EQ t_cdpos_tmp-changenr.
    IF sy-subrc EQ 0.
      SORT t_cdhdr BY objectid ASCENDING
                      changenr ASCENDING
                      udate    DESCENDING.
      DELETE ADJACENT DUPLICATES FROM t_cdhdr COMPARING objectid
                                                        changenr.
    ENDIF.
  ENDIF.

Hope this helps.

Re: Select staement with max

Former Member — Wed, 16 May 2007 20:39:09 GMT

Please ignore my previous post because you do not need to include field CHANGENR. Use the following code instead.

  DATA t_cdpos_tmp LIKE TABLE OF t_cdpos.

  IF NOT t_cdpos[] IS INITIAL.
    t_cdpos_tmp[] = t_cdpos[].
    SORT t_cdpos_tmp BY objectid
                        changenr.
    DELETE ADJACENT DUPLICATES FROM t_cdpos_tmp
      COMPARING  objectid
                 changenr.
    SELECT objectid
           username
           udate
      FROM cdhdr
      INTO TABLE t_cdhdr
      FOR ALL ENTRIES IN t_cdpos_tmp
      WHERE objectclas EQ 'MATERIAL'
      AND   objectid   EQ t_cdpos_tmp-objectid
      AND   changenr   EQ t_cdpos_tmp-changenr.
    IF sy-subrc EQ 0.
      SORT t_cdhdr BY objectid ASCENDING
                      udate    DESCENDING.
      DELETE ADJACENT DUPLICATES FROM t_cdhdr COMPARING objectid.
    ENDIF.
  ENDIF.