Sparsh Sparsh - 1 month ago 6
SQL Question

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

Answer

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