Home » SQL & PL/SQL » SQL & PL/SQL » Count and Rownum calcualtion for a query fetch  () 1 Vote
Count and Rownum calcualtion for a query fetch [message #287958] Fri, 14 December 2007 02:31 Go to next message
kshitij.raj
Messages: 7
Registered: July 2007
Junior Member
I have a table as :

Contract_Note
( Cn_No Varchar2(10),
Mkt_val Number(10),
Brk_Val Number(10),
Net_value Number(10))

creation script attached : Table_data.sql

WIth no primary Key.

Lets i have the data as

CN_NO| MKT_VAL| BRK| NET_VALUE
-----------------------------
NN01| 100| 10| 110
NN01| 200| 20| 220
NN03| 300| 30| 330
NN04| 440| 44| 484
NN05| 548| 54.8| 602.8
NN06| 445| 44.5| 489.5
NN06| 458| 45.8| 503.8
NN06| 784| 78.4| 862.4

Insert script attached

Result Set required : Table_data.sql

CN_NO| MKT_VAL| BRK| NET_VALUE| COUNT| ROW_COUNT
----------------------------------------------
NN01| 100| 10| 110| 2| 1
NN01| 200| 20| 220| 2| 2

NN03| 300| 30| 330| 1| 1
NN04| 440| 44| 484| 1| 1
NN05| 548| 54.8| 602.8| 1| 1
NN06| 445| 44.5| 489.5| 3| 1
NN06| 458| 45.8| 503.8| 3| 2
NN06| 784| 78.4| 862.4 | 3 | 3
Re: Count and Rownum calcualtion for a query fetch [message #287962 is a reply to message #287958] Fri, 14 December 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you calculate the row_count columns? What is the algorithm? What is the criteria to give 1 to a row and not another one?

Regards
Michel
Re: Count and Rownum calcualtion for a query fetch [message #287967 is a reply to message #287958] Fri, 14 December 2007 02:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the script! Why don't you try something like this:
SELECT cn_no
     , mkt_val
     , brk
     , net_value
     , max(the_count) OVER (PARTITION BY cn_no) the_count
     , the_count row_count
FROM ( SELECT cn_no
            , mkt_val
            , brk
            , net_value
            , count(*) OVER (PARTITION BY cn_no ORDER BY cn_no, mkt_val) the_count
       FROM  contract_note
       ORDER BY cn_no, mkt_val
    )
/


MHE

[Edit]Since there is no primary key, I cannot give any guarantees.

[Updated on: Fri, 14 December 2007 02:50]

Report message to a moderator

Re: Count and Rownum calcualtion for a query fetch [message #287969 is a reply to message #287958] Fri, 14 December 2007 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from CONTRACT_NOTE order by 1,2;
CN_NO     MKT_VAL        BRK  NET_VALUE
------ ---------- ---------- ----------
NN01          100         10        110
NN01          200         20        220
NN03          300         30        330
NN04          440         44        484
NN05          548       54.8      602.8
NN06          445       44.5      489.5
NN06          458       45.8      503.8
NN06          784       78.4      862.4

8 rows selected.

SQL> select cn_no, mkt_val, brk, net_value,
  2         count(*) over (partition by cn_no) "COUNT",
  3         row_number () over (partition by cn_no order by mkt_val) row_count
  4  from CONTRACT_NOTE order by 1,2;
CN_NO     MKT_VAL        BRK  NET_VALUE      COUNT  ROW_COUNT
------ ---------- ---------- ---------- ---------- ----------
NN01          100         10        110          2          1
NN01          200         20        220          2          2
NN03          300         30        330          1          1
NN04          440         44        484          1          1
NN05          548       54.8      602.8          1          1
NN06          445       44.5      489.5          3          1
NN06          458       45.8      503.8          3          2
NN06          784       78.4      862.4          3          3

8 rows selected.

Regards
Michel
Re: Count and Rownum calcualtion for a query fetch [message #287980 is a reply to message #287969] Fri, 14 December 2007 03:43 Go to previous messageGo to next message
kshitij.raj
Messages: 7
Registered: July 2007
Junior Member
what is the effect of the query on Performance?

I have around 4 table indexed properly and also calling one function in the Fetch statement.

Re: Count and Rownum calcualtion for a query fetch [message #287981 is a reply to message #287980] Fri, 14 December 2007 03:56 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no restriction so indexes are useless. It is a full table scan.

Regards
Michel
Previous Topic: varray in in-list function
Next Topic: help for Data display
Goto Forum:
  


Current Time: Thu Dec 08 14:11:44 CST 2016

Total time taken to generate the page: 0.08148 seconds