This discussion is archived
5 Replies Latest reply: Dec 4, 2012 12:30 AM by Ayham RSS

group by and order by

Ayham Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points