Thanks for the question, sparsh.
Asked: September 27, 2016 - 2:55 am UTC
Last updated: October 02, 2016 - 8:21 am UTC
Version: ora11gr2
Viewed 1000+ times
You Asked
Hi Tom
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
and Connor said...
I dont think you are ordering by what you think you are ordering by ? Lets run your test case, but also output the ordering column
SQL> drop table xyz purge;
Table dropped.
SQL> create table xyz ( contrno number , mobile number primary key);
Table created.
SQL> insert into xyz values(1003288127,123456);
1 row created.
SQL> insert into xyz values(1003288127,123457);
1 row created.
SQL> insert into xyz values(1003288127,123458);
1 row created.
SQL> insert into xyz values(1003288127,123459);
1 row created.
SQL> insert into xyz values(1003288127,123450);
1 row created.
SQL> insert into xyz values(1003288127,123451);
1 row created.
SQL> insert into xyz values(1003288127,123452);
1 row created.
SQL> insert into xyz values(1003288127,123453);
1 row created.
SQL> insert into xyz values(1003288127,123454);
1 row created.
SQL> insert into xyz values(1003288127,123455);
1 row created.
SQL>
SQL> select c.* , count(c.contrno) over ( partition by c.contrno ) as seq
2 from xyz c
3 order by count(c.contrno) over ( partition by c.contrno ) desc;
CONTRNO MOBILE SEQ
---------- ---------- ----------
1003288127 123456 10
1003288127 123457 10
1003288127 123458 10
1003288127 123459 10
1003288127 123450 10
1003288127 123451 10
1003288127 123452 10
1003288127 123453 10
1003288127 123454 10
1003288127 123455 10
10 rows selected.
SQL>
SQL>
Notice that the value is "10" for all rows, so the ordering is indeterminate. I cant really comment any further as to whether this is correct or not (ie, perhaps you want to count/order by mobile numnber, or perhaps you want to count/order by what you currently have, which might make sense when there are lots of *different* 'contrno' values.
Your test cases does not provide sufficient information to decide.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment