CODEWITHSUNDEEP
sqloracle-databasewindow-functions
Sparsh
Sparsh

Reputation: 39

How to get rows together after using analytic function

create table xyz ( contrno number , mobile number primary key); 

insert into xyz values(1003288127,123456); 
insert into xyz values(1003288127,123457); 
insert into xyz values(1003288127,123458); 
insert into xyz values(1003288127,123459); 
insert into xyz values(1003288127,123450); 
insert into xyz values(1003288127,123451); 
insert into xyz values(1003288127,123452); 
insert into xyz values(1003288127,123453); 
insert into xyz values(1003288127,123454); 
insert into xyz values(1003288127,123455);

I want rows should be arrange in descending order of count of contrno and all contrno rows should be together that means rownum should be sequential, I have written this query 

select c.* 
from xyz c 
order by count(c.contrno) over ( partition by c.contrno ) desc) t

which arranges rows based on count of contrno that is correct but not all contrno together

But when I query the rownum by below query 

select k.* from (select rownum rn ,t.* from(select c.* 
from xyz c 
order by count(c.contrno) over ( partition by c.contrno ) desc) t ) k 
where k.contrno=1003288127

Output is 

rn contrno 
1 51024 1003288127 
2 51025 1003288127 
3 51089 1003288127 
4 51090 1003288127 
5 51091 1003288127 
6 51092 1003288127 
7 51093 1003288127 
8 51094 1003288127 
9 51095 1003288127 
10 51096 1003288127 
11 51097 1003288127

So here if you see after 51024 and 51025, 51089 is starting and in between 51025 and 51089 other contrno is coming.

Please answer why it is happening and how to write query which can give output based on sequential rownum

Upvotes: 0

Views: 61

Answers (1)

Kacper
Kacper

Reputation: 4818

If I understand correctly you want to order first by count then by contrno:

order by count(c.contrno) over ( partition by c.contrno ) desc, c.contrno

or even by mobile as well

order by count(c.contrno) over ( partition by c.contrno ) desc, c.contrno,  c.mobile

Upvotes: 1

Related Questions