5 Replies Latest reply: Jul 24, 2013 11:04 AM by MadWorld RSS

    Problem with simple grouping, very annoying!

    bec4542f-4f73-4fff-bd97-f25432964c3c

      Hi all, I have a problem trying to select the maximum value of a field in a table, which looks like this:

       

      Emp_NoQuarterValue
      0000101-JAN-090
      0000101-APR-090
      0000101-JUL-090
      0000101-OCT-096
      0000101-JAN-106
      0000101-APR-106
      0000101-JUL-1012
      0000101-OCT-106
      0000101-JAN-116
      0000101-APR-116
      0000101-JUL-116
      0000101-OCT-116
      0000101-JAN-126
      0000101-APR-126
      0000101-JUL-126
      0000101-OCT-126
      0000101-JAN-136
      0000101-APR-136

       

      I'd like to return the 7th row, being the highest in the 'Value' column. So I thought

       

      select emp_no, quarter, max(value) from table group by emp_no, quarter

       

      might work - but obviously that brings back everything, because I'm grouping by the first two columns. Next, I tried

       

      select emp_no, max(quarter), max(value) from table group by emp_no

       

      But that brings back results from two different rows (APR 13 and 12).

       

      I know this is dead simple but I can't engage my brain for some reason...

        • 1. Re: Problem with simple grouping, very annoying!
          Greg Spall

          You're looking for what's called a "top-n" query ( you can search for that term for a lot of good examples).

           

          This should work with your data:

           

          with xx as (
                   select '00001' emp_no, to_date('01-JAN-09','dd-mon-yy') quarter, 0  value from dual union all
                   select '00001' emp_no, to_date('01-APR-09','dd-mon-yy') quarter, 0  value from dual union all
                   select '00001' emp_no, to_date('01-JUL-09','dd-mon-yy') quarter, 0  value from dual union all
                   select '00001' emp_no, to_date('01-OCT-09','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JAN-10','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-APR-10','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JUL-10','dd-mon-yy') quarter, 12 value from dual union all
                   select '00001' emp_no, to_date('01-OCT-10','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JAN-11','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-APR-11','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JUL-11','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-OCT-11','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JAN-12','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-APR-12','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JUL-12','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-OCT-12','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-JAN-13','dd-mon-yy') quarter, 6  value from dual union all
                   select '00001' emp_no, to_date('01-APR-13','dd-mon-yy') quarter, 6  value from dual
                ),
             yy as (
                   Select emp_no, quarter, value,
                          row_number() over (partition by emp_no order by value desc) rnum
                     from xx
                )
          Select *
            from yy
          where rnum = 1;
          

           

           

          EMP_NO QUARTER        VALUE       RNUM

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

          00001  01-JUL-10         12          1

          1 row selected.

          • 2. Re: Problem with simple grouping, very annoying!
            chris227

            select

              emp_no

            , max(quarter) keep (dense_rank last order by value nulls first)

            , max(value)

            from table

            group by emp_no

             

            Message was edited by: chris227 nulls first instead of nulls last

            • 3. Re: Problem with simple grouping, very annoying!
              Pacmann

              Hi,

               

              You can use dense_rank.. keep, or analytic functions.

               

              select max(emp_no) keep(dense_rank last order by value), max(quarter) keep(dense_rank last order by value), max(value) from table

              • 4. Re: Problem with simple grouping, very annoying!
                bec4542f-4f73-4fff-bd97-f25432964c3c

                Fantastic, thanks you all for your replies - I'm going to look more into the dense_rank function, that's a new one on me...!

                 

                Cheers again

                 

                James

                • 5. Re: Problem with simple grouping, very annoying!
                  MadWorld

                  Hi,

                   

                  This solution also works if you have more than one employee.

                   

                  SELECT emp_alias.emp_no,

                    emp_alias.quarter,

                    emp_alias.value

                  FROM emp emp_alias,

                    (SELECT emp_no, MAX(value) max_value FROM emp GROUP BY emp_no

                    ) in_line_view

                  WHERE emp_alias.value = in_line_view.max_value

                  AND emp_alias.emp_no = in_line_view.emp_no