select entire fields with aggregate functions (max,...)

[Former Member]

Hi,

I'm very new to ABAP

How to solve the following situation in abap:

http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-ano...

In contrast, how to apply the following mysql code(solution in previous topic) in abap:

SELECT tt.*
FROM topten tt
INNER JOIN
    (
    SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home
    ) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime

I hope you help me with these situations:

     - how to select columns NOT in group by clause

     - how to use JOIN with subquery

Your help be will be much appreciated, Thanx

[Former Member]

Hi,

Join

It is used to JOIN two DATABASE tables

having some COMMON fields.

Join

SELECT a~pernr a~begda a~endda a~branc b~nachn b~vorna

FROM pa0023 as a

   INNER JOIN pa0002 as b 

   ON a_pernr = b~pernr

INTO TABLE ifinal

WHERE a~pernr IN ( select pernr from pa0023

where aedtm = p_aedtm and

uname ne 'KA51151'

pls check this link http://help.sap.com/saphelp_47x200/helpdata/en/dc/dc7614099b11d295320000e8353423/frameset.htm

[Former Member]

Thanx for your reply,

but what I meant is how to JOIN a table with a Subquery (NOT another table) just like mentioned earlier:

.... JOIN (SELECT ..... FROM ....) ON ...

Is that possible in ABAP?

[former_member220538]

Hi,

Try this

SELECT DISTINCT *

       INTO TABLE <itab>
       FROM topten
       WHERE datetime IN ( SELECT MAX( datetime ) FROM topten GROUP BY home ).

[Former Member]

Jeffin George wrote:
 Hi,
Try this 
SELECT DISTINCT * 
       INTO TABLE <itab>
       FROM topten
       WHERE datetime IN ( SELECT MAX( datetime ) FROM topten GROUP BY home ).

Thanx for your help,

unfortunately this didn't solve the issue

To make things more clear I've attached an Image for the table with sample data alonge a SELECT with the aggregate max applied and another with your code

the code resulted in unwanted data, however it seemed work with the example in the original post link.

Though I know the how to achieve what I want, I can't get it done in ABAP

[freek_cavens2]

Hi Ibrahim,

To achieve this, you will need two select statements

The first select will give you a table with the max datetime per home

SELECT home max( datetime )

                  FROM topten

                  into CORRESPONDING FIELDS OF TABLE lt_groupedtt

                  GROUP BY home.

The second select will give you all data for the corresponding entries :

SELECT *

            INTO CORRESPONDING FIELDS OF TABLE lt_topten

            FROM topten

            FOR ALL ENTRIES IN lt_groupedtt

            WHERE home eq lt_groupedtt-home

              and datetime eq lt_groupedtt-maxdatetime.

Check the syntax for 'FOR ALL ENTRIES' (this will be very helpfull, since this is how you will usually handle these type of selects in ABAP).  You also need to declare the structures and tables fro lt_topten and lt_groupedtt.

Regards,

Freek

[freek_cavens2]

Hi Ibrahim,

If you want to do it in one select statement, you could off course also use a query with a 'Where exists' subquery.  The syntax for this is quite similar to normal SQL syntax.

Regards,

Freek

[Former Member]

Hi Freek,

Thanx for your help, I tried to make it in single statement with no luck,

can you provide me the code with one statement please

[freek_cavens2]

Hi Ibrahim,

This is the code, if you want to get all data of the purchase orders on the max date for each vendor. 

DATA :
   lt_ekko TYPE TABLE OF ekko.

START-OF-SELECTION.

   SELECT * FROM ekko AS a
          INTO CORRESPONDING FIELDS OF TABLE lt_ekko
          WHERE EXISTS ( SELECT b~lifnr
                         FROM ekko AS b
                         WHERE b~lifnr EQ a~lifnr
                         GROUP BY b~lifnr
                         HAVING MAX( b~aedat ) = a~aedat ).

Regards,

Freek

[Former Member]

Thanx Freek for your help, it worked fine

[arindam_m]

Hi,

If DB specific SQL's are not a problem try using the ADBC classes to run your query just like you do on a SQL client. Should give you more flexibility. Check the link below.

http://help.sap.com/abapdocu_702/en/abenadbc.htm

Cheers,

Arindam

[RaymondGiuseppi]

Try

SELECT DISTINCT * INTO TABLE itab
   FROM topten AS a
   WHERE NOT EXISTS ( SELECT * FROM topten AS b WHERE b~home = a~home AND b~datetime > a~datetime ).

or

 SELECT DISTINCT * INTO TABLE itab
   FROM topten AS a
   WHERE a~datetime IN ( SELECT MAX( b~datetime ) FROM topten AS b WHERE b~home = a~home ).

(Or could you explain your MySQL code ?)

Regards,

Raymond

[Former Member]

Thanks Raymond, it worked nice for me

I've tried removing DISTINCT and it gave me the same result, so I'm wondering why using DISTINCT.

I guess using DISTINCT with (*) has no effect in a table with a primary key, am I wrong?

[RaymondGiuseppi]

Yes this one was only a hint as I presumed your next question, if you want OPEN-SQL to remove duplicates, you should replace the * with the unicity keys (but may be required to read again database for other fields)

SELECT DISTINCT home datetime

Else you could add a second subquery to restrict (if recid is primary key)

AND NOT EXISTS ( SELECT * FROM topten AS c WHERE c~home = a~home AND c~datetime = a~datetime  AND c~recid > a~recid).  

But report's maintainability will be poor, and load on database server high, it's time to work with the internal table (SORT and DELETE ADJACENT RECORDS COMPARING) (Depending on actual requirement, you could also use aggregate function to group duplicates in a single record)

Regards,

Raymond

[Former Member]

Thanks Raymond for your great help, worked exactly as I wanted

wheeow, didn't expect to be such complicated query

after wrapping all together I came up with this query, hope I can refine it into a simpler one

SELECT * INTO it_homes FROM zhomes AS a
           WHERE NOT EXISTS
                   ( SELECT *
                     FROM   zhomes AS b
                     WHERE  b~homeid = a~homeid AND b~cdate > a~cdate )
           AND NOT EXISTS
                   ( SELECT *
                     FROM zhomes AS c
                     WHERE c~homeid = a~homeid AND c~cdate = a~cdate  AND c~id > a~id ).

[RaymondGiuseppi]

Yes you could write something like (I did not check syntax)

SELECT * INTO it_homes FROM zhomes AS a
   WHERE NOT EXISTS (
     SELECT * FROM zhomes AS b
       WHERE b~homeid = a~homeid
         AND ( b~cdate > a~cdate OR ( b~cdate = a~cdate  AND b~id > a~id ) ).

But should you, have a thought for the SQL optimizer and the poor guy that will maintain the program after you...

Regards,

Raymond

[Former Member]

Thanx Raymond, you are the man

[Former Member]

Thank you all for your help, I've learnt more than solving my issue

Freek's and Raymond's solutions worked great, however I'm facing another issue

sometimes there is more than a record with the same value for the latest date "max(date)" for a specific "home",

how to eliminate duplicates from result with same 'max date' for the same home (something like DISTINCT homeid)

in other words, I need to select only one entry for the latest date for each home (maybe the one with latest recid)

hope I managed to make it clear

here is a sample result with Freek's and Raymond's codes applied, I want to eliminate records like the red one, any help will be appreciated

RECID        HOMEID     DATE           PLAYER          RESOURCES     
00000001      00000010      04.03.2009      JOHN                100
00000012      00000010      04.03.2009      JOHN                599
00000002      00000011      04.03.2009      JULIET               250
00000003      00000012      04.03.2009      BORAT              300
00000008      00000013      01.01.2009      BORAT              400
00000010      00000014      04.04.2009      PETER              200

Thanx again