topic Re: select entire fields with aggregate functions (max,...) in Application Development and Automation Discussions

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

Former Member — Sun, 29 Sep 2013 19:06:29 GMT

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-another-column-in-sql

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

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

Former Member — Mon, 30 Sep 2013 04:54:41 GMT

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

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

former_member220538 — Mon, 30 Sep 2013 05:42:35 GMT

Hi,

Try this

SELECT DISTINCT *

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

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

Former Member — Mon, 30 Sep 2013 13:06:22 GMT

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?

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

Former Member — Mon, 30 Sep 2013 14:29:03 GMT

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

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

freek_cavens2 — Mon, 30 Sep 2013 19:42:36 GMT

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

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

arindam_m — Mon, 30 Sep 2013 23:39:57 GMT

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

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

freek_cavens2 — Tue, 01 Oct 2013 08:37:13 GMT

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

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

Former Member — Tue, 01 Oct 2013 15:45:27 GMT

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

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

RaymondGiuseppi — Wed, 02 Oct 2013 13:24:56 GMT

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 ).

 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

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

freek_cavens2 — Wed, 02 Oct 2013 14:45:23 GMT

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

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

Former Member — Thu, 03 Oct 2013 03:03:55 GMT

Thanx Freek for your help, it worked fine

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

Former Member — Thu, 03 Oct 2013 03:09:37 GMT

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?

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

Former Member — Thu, 03 Oct 2013 03:11:54 GMT

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

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

RaymondGiuseppi — Thu, 03 Oct 2013 06:11:22 GMT

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

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

Former Member — Thu, 03 Oct 2013 12:40:29 GMT

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 ).

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

RaymondGiuseppi — Thu, 03 Oct 2013 12:51:40 GMT

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

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

Former Member — Thu, 03 Oct 2013 18:32:14 GMT

Thanx Raymond, you are the man