5 Replies Latest reply: Dec 4, 2012 2:30 AM by Ayham RSS

    group by and order by

    Ayham
      l have the folloiwing table and data and expected output with my rules
      drop table test_new;
      create table test_new (id number(9), loc1 number(9), loc2 number(9), percentage number(9));
      insert into test_new values (1,1,2,0);
      insert into test_new values(2,1,3,10);
      insert into test_new values(3,1,4,5);
      insert into test_new values(4,1,5,45);
      insert into test_new values(5,2,3,0);
      insert into test_new values(6,2,4,90);
      insert into test_new values(7,2,5,0);
      insert into test_new values(8,3,4,0);
      insert into test_new values(9,3,5,0);
      insert into test_new values(10,4,5,40);
      insert into test_new values(11,7,5,0);
      insert into test_new values(12,9,4,0);
      insert into test_new values(13,10,5,90);
      insert into test_new values(14,11,5,70);
      insert into test_new values(15,1,15,45);
      The query show that
      id  loc1 loc2  percentage
      4    1    15       45
      2    1    3        10
      6    2    4        90
      13   10   5        90
      Rules:

      1- show id,loc1,loc2 percentage where percentage greater than zero.
      2- remove any redendance of data in column loc2 so the remove row that has lower value of percentage.
      3- Most importantt sort data according percentage column based on grouping column loc1 asc.
      4- many thanks for you.

      not same this reults:

      Not this one
      id   loc1 loc2  percentage
      2    1    3       10
      15    1    15      45
      6    2    4       90
      13   10   5       90
      that happen when i used this query
      select t1.* from test_new t1
      left join test_new t2
      on t1.loc2 = t2.loc2 and t1.percentage < t2.percentage
      where t1.percentage > 0 and t2.loc2 is null
      order by t1.percentage
      regards

      Edited by: Ayham on Dec 3, 2012 11:52 PM
        • 1. Re: group by and order by
          Purvesh K
          Ayham wrote:
          insert into test_new values(4,1,5,45);
          >
          id loc1 loc2 percentage
          4 1 15 45
          2 1 3 10
          6 2 4 90
          13 10 5 90
          ID 4 has LOC2 as 5 and not 15. Is that a Typo on your part? If not, please explain how do we get 15 for ID 4.

          You do not need a self join to order the percentages. Analytic functions can ease the job for you.
          select id, loc1, loc2, percentage
            from (
                  select id, loc1, loc2, percentage, dense_rank() over (partition by loc2 order by percentage desc) r_loc2
                    from test_new
                   where percentage != 0
                 )
           where r_loc2 = 1
           order by percentage;
          
          ID                     LOC1                   LOC2                   PERCENTAGE             
          ---------------------- ---------------------- ---------------------- ---------------------- 
          2                      1                      3                      10                     
          15                     1                      15                     45                     
          13                     10                     5                      90                     
          6                      2                      4                      90
          Output is considering your sample data.
          • 2. Re: group by and order by
            AlbertoFaenza
            Hi Ayham,

            your post is a bit confusing.

            I still don't understand which of the 2 output you want.

            I assume that the output you want is this one:
            id  loc1 loc2  percentage
            4    1    15       45
            2    1    3        10
            6    2    4        90
            13   10   5        90
            but if I look at your insert it seems that for id=4 you have
            insert into test_new values(4,1,5,45);
            loc1= 1 and loc2=15.

            How could you get the output above?

            Regards.
            Al
            • 3. Re: group by and order by
              Ayham
              many thanks,
              Sorry that was typo.

              But i want the results like this.
              id  loc1 loc2  percentage
              15    1    15       45
              2    1    3        10
              13   10   5        90
              6    2    4        90
              not like.
              ID                     LOC1                   LOC2                   PERCENTAGE             
              ---------------------- ---------------------- ---------------------- ---------------------- 
              2                      1                      3                      10                     
              15                     1                      15                     45                     
              13                     10                     5                      90                     
              6                      2                      4                      90
              just the ordering is not ok for id 2 and id 15 why? because the percentage for id 15 is 45 and for id 2 is 10 these two ids in group on loc1 that is 1. and id 13 in different group 10 but id 6 in group 2. that mean the sorting for memebrs inside groups.

              regards
              Ayham

              Edited by: Ayham on Dec 4, 2012 12:29 AM
              • 4. Re: group by and order by
                AlbertoFaenza
                Hi Ayham,

                order by loc1, percentage DESC if this is what you want.

                Also you can use analytic function:
                WITH mydata AS (SELECT id
                                     , loc1
                                     , loc2
                                     , percentage
                                     , ROW_NUMBER () OVER (PARTITION BY loc2 ORDER BY percentage DESC, id) rn
                                  FROM test_new
                                 WHERE percentage != 0)
                  SELECT id, loc1, loc2, percentage
                    FROM mydata
                   WHERE rn = 1
                ORDER BY loc1, percentage DESC;
                
                        ID       LOC1       LOC2 PERCENTAGE
                ---------- ---------- ---------- ----------
                        15          1         15         45
                         2          1          3         10
                         6          2          4         90
                        13         10          5         90
                Regards.
                Al
                • 5. Re: group by and order by
                  Purvesh K
                  Sorry, I overlooked the requirement 3 in your OP. This does the job for you:
                  select id, loc1, loc2, percentage
                    from (
                          select id, loc1, loc2, percentage, dense_rank() over (partition by loc2 order by percentage desc) r_loc2
                            from test_new
                           where percentage != 0
                         )
                   where r_loc2 = 1
                   order by loc1, loc2 desc, percentage;
                  
                  ID                     LOC1                   LOC2                   PERCENTAGE             
                  ---------------------- ---------------------- ---------------------- ---------------------- 
                  15                     1                      15                     45                     
                  2                      1                      3                      10                     
                  6                      2                      4                      90                     
                  13                     10                     5                      90
                  Or probably this:
                  select id, loc1, loc2, percentage
                    from (
                          select id, loc1, loc2, percentage, dense_rank() over (partition by loc2 order by percentage desc) r_loc2
                            from test_new
                           where percentage != 0
                         )
                   where r_loc2 = 1
                   order by loc1, percentage desc;
                  
                  ID                     LOC1                   LOC2                   PERCENTAGE             
                  ---------------------- ---------------------- ---------------------- ---------------------- 
                  15                     1                      15                     45                     
                  2                      1                      3                      10                     
                  6                      2                      4                      90                     
                  13                     10                     5                      90
                  Edited by: Purvesh K on Dec 4, 2012 2:08 PM