5 Replies Latest reply on Dec 4, 2012 8:38 AM by Purvesh K

# group by and order by

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