9 Replies Latest reply: Jul 8, 2014 9:35 AM by Frank Kulash RSS

    help with this group by

    mgaldames

      Hello there,

       

      I need your help to do a group by in a query (for ORACLE 11g) for some columns and in one particular column get the last (or recent value of that column)... this is the example of my table:

       

      CODEMOBILEAMOUNTCOUNTRYRECORD_DATE
      10009999999910USA18-JUN-14
      1000999999995ITALY13-JUN-14
      20009999999920FRANCE16-JUN-14
      2000888888883GERMANY17-JUN-14
      20008888888811FRANCE14-JUN-14
      10008888888832CANADA10-JUN-14

       

      So the result of my query should do a "group by" by CODE, MOBILE and SUM the AMOUNT and give the last country registered in this form:

       

      CODEMOBILEAMOUNTCOUNTRY
      10009999999915USA
      20009999999920FRANCE
      10008888888832CANADA
      20008888888814GERMANY

       

      Is just an example, i can do the query and group by statement but i can not show just the last country (if i think in the recent record of RECORD_DATE column).

       

      Can you help me with this pls? i don't know a lot of things about oracle 11g

       

      Thanx so much in advance.

       

      Best regards,

      Mauro

        • 1. Re: help with this group by
          Frank Kulash

          Hi, Mauro,

           

          So, you don't want MAX (record_date); you want the country that's on the same row as the MAX (record_date); is that it?

          That sounds like a job for the aggregate LAST fucntion.

          Since I don't have a copy oof your table, I'll use scott.emp (which is probably on your system) to illustrate.

           

          SELECT    deptno, job

          ,         SUM (sal)      AS total_sal

          ,         MIN (ename) KEEP (DENSE_RANK LAST ORDER BY hiredate)

                                   AS most_recent

          FROM      scott.emp

          GROUP BY  deptno, job

          ORDER BY  deptno, job

          ;

          Output:

           

              DEPTNO JOB        TOTAL_SAL MOST_RECENT

          ---------- --------- ---------- -----------

                  10 CLERK           1300 MILLER

                  10 MANAGER         2450 CLARK

                  10 PRESIDENT       5000 KING

                  20 ANALYST         6000 SCOTT

                  20 CLERK           1900 ADAMS

                  20 MANAGER         2975 JONES

                  30 CLERK            950 JAMES

                  30 MANAGER         2850 BLAKE

                  30 SALESMAN        5600 MARTIN

           

          What if there happens to be a tie for the most recent row, that is, if the MAX (record_date) in a group occurs one 2 or more rows, with different countries?

           

          I hope this answers your question.

          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.

          Point out where the query above is producing the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.

          If you modify the query at all, post your modified version.

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

           

           

          See the forum FAQ: https://community.oracle.com/message/9362002#9362002

          • 2. Re: help with this group by
            Manik

            Following Frank's solution, you would require this...

             

            with t as (select 1000  code,  99999999 mob  , 10 amount,    'USA'  country ,  to_date('18-Jun-2014','DD-MON-YYYY') dt from dual union all

            select 1000  ,  99999999   , 5 ,   'ITALY'  ,  to_date('13-Jun-2014','DD-MON-YYYY') from dual union all

            select 2000  ,  99999999   , 20 ,   'FRANCE' ,   to_date('16-Jun-2014','DD-MON-YYYY') from dual union all

            select 2000  ,  88888888   , 3  ,  'GERMANY'  ,  to_date('17-Jun-2014','DD-MON-YYYY') from dual union all

            select 2000  ,  88888888   , 11 ,   'FRANCE'  ,  to_date('14-Jun-2014','DD-MON-YYYY') from dual union all

            select 1000  ,  88888888   , 32 ,   'CANADA'   , to_date('10-Jun-2014','DD-MON-YYYY') from dual)

              SELECT code,

                     mob,

                     SUM (amount),

                     MAX (country) KEEP (DENSE_RANK LAST ORDER BY dt)

                FROM t

            GROUP BY code, mob;

             

            1000 88888888 32 CANADA

            1000 99999999 15 USA

            2000 88888888 14 GERMANY

            2000 99999999 20 FRANCE

             

             

            Cheers,

            Manik.

            • 3. Re: help with this group by
              Manik

              If you are on Oracle 11gr2 you can try this one.. (without analytical functions..)

              ---

               

              with t as (select 1000  code,  99999999 mob  , 10 amount,    'USA'  country ,  to_date('18-Jun-2014','DD-MON-YYYY') dt from dual union all

              select 1000  ,  99999999   , 5 ,   'ITALY'  ,  to_date('13-Jun-2014','DD-MON-YYYY') from dual union all

              select 2000  ,  99999999   , 20 ,   'FRANCE' ,   to_date('16-Jun-2014','DD-MON-YYYY') from dual union all

              select 2000  ,  88888888   , 3  ,  'GERMANY'  ,  to_date('17-Jun-2014','DD-MON-YYYY') from dual union all

              select 2000  ,  88888888   , 11 ,   'FRANCE'  ,  to_date('14-Jun-2014','DD-MON-YYYY') from dual union all

              select 1000  ,  88888888   , 32 ,   'CANADA'   , to_date('10-Jun-2014','DD-MON-YYYY') from dual)

                SELECT code,

                       mob,

                       SUM (amount) amount,

                       REGEXP_SUBSTR (LISTAGG (country, ',') WITHIN GROUP (ORDER BY dt desc),

                                      '[^,]+',

                                      1)

                          country

                  FROM t

              GROUP BY code, mob;

               

              CODE MOB AMOUNT COUNTRY

              1000 88888888 32 CANADA

              1000 99999999 15 USA

              2000 88888888 14 GERMANY

              2000 99999999 20 FRANCE

               

              Cheers,

              Manik.

              • 4. Re: help with this group by
                2683628

                Hi Frank,


                Can you please explain to me, 'KEEP' in your example.

                • 5. Re: help with this group by
                  Roger

                  The KEEP (DENSE RANK LAST ORDER by dt)

                   

                  is used to reduce the elements on which the MAX function should be applied. In your case the data is grouped by code and mob which in case of 1000, 99999999 includes three rows (USA with an amount of 10, ITALY with an amount of 5 and CANADA with 32) now to show "a" country we could just do a MAX (or MIN) on the country column which will always show USA (or CANADA) even though the last recent row is perhaps another one. Here comes the KEEP ... in place. Those 3 rows are ordered by dt (ascending here) and the MAX function is applied on the rows with the lowest date of the group instead of on all 3.

                   

                  hth

                  • 6. Re: help with this group by
                    Frank Kulash

                    Hi,

                     

                    8659790b-34b8-4730-bcea-1f64d138e5da wrote:

                     

                    Hi Frank,


                    Can you please explain to me, 'KEEP' in your example.

                    KEEP is just a keyword needed when you use the FIRST or LAST functions, which are described in the SQL language manual.  See:

                    LAST

                    When an aggregate function, such as MIN (country), is followed by the keyword KEEP, the parser knows (and we know) that it's not operating on the whole group, but only on the FIRST or LAST part of that group.

                    • 7. Re: help with this group by
                      chris227

                      Manik wrote:

                       

                      If you are on Oracle 11gr2 you can try this one.. (without analytical functions..)

                       

                      MAX (country) KEEP (DENSE_RANK LAST ORDER BY dt) used with group by is the aggregate version of this function not the analytical one.

                       

                      Could you please explain for what reason someone should ever used some regexp/listagg apporach instead (flawed for example if the values contain the delimiters)?

                      • 8. Re: help with this group by
                        mgaldames

                        Thanks you all for ur answers.

                         

                        I tried Frank and Manik solutions and both works well.

                         

                        About what Frank said "What if there happens to be a tie for the most recent row, that is, if the MAX (record_date) in a group occurs one 2 or more rows, with different countries?"  I didn't think in that possibility but it could happened... data in RECORD_DATE contain hh:mm:ss that could help to have the most recent... right?

                         

                        What is the difference of using Frank or Manik approach? When to use the 'KEEP ...' approach over 'REGEXP/LISTAGG'?

                         

                        Thank you all for your help

                        • 9. Re: help with this group by
                          Frank Kulash

                          Hi,

                           

                           

                          mgaldames wrote:

                           

                          Thanks you all for ur answers.

                           

                          I tried Frank and Manik solutions and both works well.

                           

                          About what Frank said "What if there happens to be a tie for the most recent row, that is, if the MAX (record_date) in a group occurs one 2 or more rows, with different countries?"  I didn't think in that possibility but it could happened... data in RECORD_DATE contain hh:mm:ss that could help to have the most recent... right?

                          ...

                          If you're capturing record_date by using TRUNC (SYSDATE), then all entries made on the same calendar day will have the same value.

                          If you're capturing record_date by using SYSDATE (without TRUNC) then only entries made at the same second will have the same value, so ties will be less likely, but they are still possible, even on a single-user system.

                          Regardless of how likely that situation is, how do you want to handle it?

                           

                          What is the difference of using Frank or Manik approach? When to use the 'KEEP ...' approach over 'REGEXP/LISTAGG'?

                          The MIN ... LAST approach that I used will display the first country (in alphabetic order) when ther is a tie. (That's what MIN is doing there.)

                          The REGEXP ... LISTAGG approach will pick 1 country arbitrarily.

                          Either approach can be modified.