-
Adding new column for Transaction counter from SQL query
I have a table trans with all transations in it
transid,prod,amt are the three columns
select * from trans; is
transid prod amt
100 2 2.2
100 4 3.2
100 3 8.7
100 3 6.5
101 3 2.5
101 14 18.1
101 ... ...
I want to add a column at the end of the result set such that i can have a counter for all lines of the trans number 100. like
select * from trans where !!!! ;
transid prod amt transln
100 2 2.2 1
100 4 3.2 2
100 3 8.7 3
100 3 6.5 4
101 3 2.5 1
101 14 18.1 2
101 ... ... ...
So how can i achieve this from one sql statement from the trans table , can i achieve this using some pseudo column or any other function available in Oracle. I don'nt wana use pl/sql procedure
An elephant is a mouse with an operating system.
-
Is this what you mean
Code:
SQL> l
1 SELECT
2 deptno,
3 dname,
4 (SELECT COUNT(*)
5 FROM emp
6 WHERE deptno = d.deptno) "No Employees"
7* FROM dept d
SQL> /
DEPTNO DNAME No Employees
---------- -------------- ------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Code:
select transid, prod, amt,
row_number() over (partition by transid order by rowid) transln
from trans;
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
I just want the trans line number at the end of each line which I don'nt have it in my current table.
What you told me is the total count of the lines in each transaction.
An elephant is a mouse with an operating system.
-
Thanks a lot Tommazz it worked!!!!
An elephant is a mouse with an operating system.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|