SQL usage about group by and top 1

[Former Member]

Dear expert.

  I want to that:

     1. order by bkpf~budat DESCENDING

                      bkpf~gjahr DESCENDING

                      bkpf~belnr DESCENDING

                      bkpf~awkey DESCENDING

     2. group by (bkpf~bukrs  bkpf~blart bkpf~budat)

     3. in the group get the first row, i try to use max() aggregate, but it can't meet my requirement

  Is there SQL statement instaed below code( need finish this in only one SQL statement) :

  SELECT bsim~matnr
         bkpf~bukrs
         bkpf~blart
         bkpf~budat
         bkpf~gjahr
         bkpf~belnr
         bkpf~awkey
     INTO TABLE itab_bkpf
     FROM bkpf
     INNER JOIN bsim ON bsim~belnr = bkpf~belnr
                    AND bsim~gjahr = bkpf~gjahr  
     ORDER BY bkpf~budat DESCENDING
              bkpf~gjahr DESCENDING
              bkpf~belnr DESCENDING
              bkpf~awkey DESCENDING
    .

    DELETE ADJACENT DUPLICATES FROM itab_bkpf COMPARING matnr bukrs blart.

[Former Member]

Hi,

Use Cursor Feature of SQL.

Reagrds,

Ravi Pratap Singh

[rosenberg_eitan]

Hi,

There is an old saying "Divide and conquer".

I noticed that (at least on our machine) there is no index on BSIM with BELNR , GJAHR .

I do not know what is your requirment but if you need to find documents that contain specific matnr

you can do it in two steps first select from BSIM and the use the result on BKPF .

Regards.

[Former Member]

hi etan,

I want to get last post fi document of material. i know that i can use 2 SQL statements to do it. but i want to know that  is there 1 SQL statement to do it.

you can't understand my requirement, i can't show you another simple example:

there are a inner table like this:

DATA: BEGIN OF wa_bsim

               matnr LIKE bsim-matnr,

               budat LIKE bsim-budat,

               dmbtr LIKE bsim-dmbtr,

           END    OF wa_bsim,

           itab_bsim LIKE TABLE OF wa_bsim.

1. if get one material we can use SQL:

select single matnr budat dmbtr

               into itab_bsim

               from bsim

               where matnr = p_matnr-low. " p_matnr is a parameter.

               order by budat descending

                            dmbtr descending.

2. if get more material i want to use SQL like that:

select matnr max(budat) max(dmbtr)

               into itab_bsim

               from bsim

               where matnr IN p_matnr " p_matnr is a parameter

               group by matnr

               order by budat desceding.

but the result isn't my need. i want to get one material last post date, and if the last post date is same, and get the biggest dmbtr.  the result give the biggest post date and biggest dmbtr. i want to do it in 1 SQL statement. any idea?

[Former Member]

Hi,

I think it is difficult to get the last value in the select, you can use the Loop,End loop and get the value with the combination.

[Former Member]

Thank you kiran!

[rosenberg_eitan]

Hi,

Try:

DATA: p_matnr TYPE RANGE OF matnr .

  DATA: itab_bsim TYPE TABLE OF bsim .

  SELECT * INTO TABLE itab_bsim
  FROM bsim
  WHERE
    matnr IN p_matnr .

  SORT itab_bsim BY matnr budat DESCENDING .

  DELETE ADJACENT DUPLICATES FROM itab_bsim COMPARING matnr .

[Former Member]

Hi Eitan, thanks your kindly reply. you are right. but i am searching for a SQL statement (One SQL satement) to do it.

[philipdavy]

Hello Zheng,

Please find the below code.

DATA: BEGIN OF WA_BKPF,

        MATNR TYPE BSIM-MATNR,

        BUKRS TYPE BKPF-BUKRS,

        BUDAT TYPE BKPF-BUDAT,

        GJAHR TYPE BKPF-GJAHR,

        BELNR TYPE BKPF-BELNR,

        AWKEY TYPE BKPF-AWKEY,

END OF WA_BKPF.

SELECT   BSIM~MATNR

                    BKPF~BUKRS

          MAX( BKPF~BUDAT ) AS BUDAT

          MAX( BKPF~GJAHR ) AS GJAHR

          MAX( BKPF~BELNR ) AS BELNR

          MAX( BKPF~AWKEY ) AS AWKEY

          INTO WA_BKPF

          FROM BKPF

          INNER JOIN BSIM ON BSIM~BELNR = BKPF~BELNR

                     AND BSIM~GJAHR = BKPF~GJAHR

          GROUP BY MATNR BUKRS

          ORDER BY

                   BUDAT DESCENDING

                   GJAHR DESCENDING

                   BELNR DESCENDING

                   AWKEY DESCENDING.

WRITE: / WA_BKPF-MATNR, WA_BKPF-BUKRS, WA_BKPF-BUDAT, WA_BKPF-GJAHR, WA_BKPF-BELNR, WA_BKPF-AWKEY.

ENDSELECT.

Regards,

Philip.

[Former Member]

Hi Philp,

     First, thank you for reply,but you don't understand my mean,  i want to get :

1.the max "budat" row

2.if the budat hava more than 2 in the same, and get the max "gjahr" row

3.....etc

max 'gjahr'  must base on the max 'budat'.

it mean that under all value must in the same db row

[Former Member]

Dear  everyone,

     I found the solution is:

DATA: BEGIN OF wa_bsim,
             matnr LIKE bsim-matnr,
             budat LIKE bsim-budat,
             dmbtr LIKE bsim-dmbtr,
          END OF wa_bsim,
          itab_bsim LIKE TABLE OF wa_bsim,
          count TYPE I.

SELECT outer_bsim~matnr
       outer_bsim~budat
       MAX( outer_bsim~dmbtr )
    FROM bsim AS outer_bsim
    INTO TABLE itab_bsim
    WHERE outer_bsim~budat = ( SELECT  MAX( inner_bsim~budat )
                                    FROM bsim as inner_bsim
                                    WHERE inner_bsim~matnr = outer_bsim~matnr
                              )
    GROUP BY outer_bsim~matnr outer_bsim~budat.

DESCRIBE TABLE itab_bsim LINES count.
WRITE: / 'LINES：', count.
LOOP AT itab_bsim INTO wa_bsim.
  WRITE: / wa_bsim-matnr, wa_bsim-budat, wa_bsim-dmbtr.
ENDLOOP.

[rosenberg_eitan]

Hi,

At some point I wrote to you about nested queries and then I delete it.

The resons:
What happen if there is multiple documents in the same date ?
Who said that the maximum of  "Amount in Local Currency" is the last value per date ?

Regards.

[Former Member]

Hi.

  Thank you for reply. this just a test SQL , just easy to show my requirement. i know that the nested SQL  i wrote is not conform the bussiness logic. I just want to konw how can get the max colume value base on other max colume result.

[rosenberg_eitan]

Hi,

You mean it is was just for fun ?

Well.... I am not smiling.

You should have told that in the opening question.

Regards .