Oops.. too many rownums... try this:
Select rownum, op_id, mcount, muv, bv, sdrbv from (
Select
op_id,
sum(msg_count) mcount,
msg_unit_value muv,
sum(billed_value) bv,
sum(sdr_billed_value) sdrbv
from op_billing
where cycle = 45
and op_id in (1,3,4)
and type = 1
group by op_id, msg_unit_value
)
-----Original Message-----
From: TABAKU Dritan
[mailto:oracledba-ezmlmshield-x42854810.[Email address protected]
Sent: 27 December 2007 10:19
To: LazyDBA Discussion
Subject: RE: Incremental rownum !
It generates error because the rownum is not found in Group By
expressions.
________________________________________
Dritan TABAKU
BCCS Unit, IT Department
AMC, ALBANIA
mobile:+355 (0) 682053050
e-mail:: [Email address protected]
-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x40942394.[Email address protected]
Sent: Thursday, December 27, 2007 11:19 AM
To: LazyDBA Discussion
Subject: RE: Incremental rownum !
Something like this:
Select rownum, op_id, mcount, muv, bv, sdrbv from (
Select
Rownum,
op_id,
sum(msg_count) mcount,
msg_unit_value muv,
sum(billed_value) bv,
sum(sdr_billed_value) sdrbv
from op_billing
where cycle = 45
and op_id in (1,3,4)
and type = 1
group by op_id, msg_unit_value
)
HTH,
John
-----Original Message-----
From: TABAKU Dritan
[mailto:oracledba-ezmlmshield-x53889704.[Email address protected]
Sent: 27 December 2007 10:08
To: LazyDBA Discussion
Subject: RE: Incremental rownum !
Dear all
select
op_id,
sum(msg_count),
msg_unit_value,
sum(billed_value),
sum(sdr_billed_value)
from op_billing
where cycle = 45
and op_id in (1,3,4)
and type = 1
group by op_id, msg_unit_value
In the above query how can I find out the rownum in incremental mode so
the first record selected should have rownum = 1 the second one =2 and
so on.
Thanks again
________________________________________
Dritan TABAKU
BCCS Unit, IT Department
AMC, ALBANIA
mobile:+355 (0) 682053050
e-mail:: [Email address protected]
-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x80360492.[Email address protected]
Sent: Thursday, December 27, 2007 11:06 AM
To: LazyDBA Discussion
Subject: RE: Incremental rownum !
Try this.
You can add a where clause as appropriate to the inner select.
select rownum, history_id from (select rownum, history_id from
cust_history order by fld1);
HTH
John
-----Original Message-----
From: TABAKU Dritan
[mailto:oracledba-ezmlmshield-x30492089.[Email address protected]
Sent: 27 December 2007 09:45
To: LazyDBA Discussion
Subject: Incremental rownum !
Dear all,
In following sql rownum display the row_id where the record is found;
Select histroy_id, rownum from cust_history
output
histroy_id rownum
----------------------------
5 20
8 30
9 33
How can I select row in incremental order so for id = 5 rownum = 1 and
so on for id=8 rownum = 2 etc?
For me is not important to have rownum as they are in table I need to
have them as index 1, 2 , 3, 4 , 5 for each record from history_id.
Thanks
________________________________________
Dritan TABAKU
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
Oracle LazyDBA home page