| Aggregate and select in same query? [message #306303] |
Thu, 13 March 2008 10:47  |
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
|
Junior Member |
|
|
I have this table with billions of transaction records. Each record has an account number, a transaction number and a balance.
For each transaction on an account, a new record is added to the table with the account number, an incremented transaction number and the current balance. When the account is closed the balance is set to zero. There might be several transactions on an account after it is closed.
The table could look something like this:
row accntno transno balance
1 100 11 $ 475
2 103 27 $ 0
3 103 26 $ 0
4 103 25 $ 25669
5 103 24 $ 24330
6 117 33 $ 1024
For each accntno I want the highest transno with a positive (or non zero) balance TOGETHER with the actual balance for that transno. In other words, I want to find the latest known balance before the account was closed. In the example I want the data contained in row number 4 for account number 103.
Since this table is enormous I don’t want to traverse it twice, but how can I achieve this?
Currently I am only able to do it in two steps like:
Query 1:
SELECT accntno, MAX(transno)
FROM transtbl
WHERE balanc > 0
GROUP BY accntno, transno;
Query 2:
SELECT accntno, transno, balance
FROM query1, transtbl
WHERE (query1.accntno = transtbl.accntno) AND (query1.transno = transtbl.transno) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Aggregate and select in same query? [message #306617 is a reply to message #306589] |
Sat, 15 March 2008 02:09  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
select acctno, transno, balance
from ( select acctno, transno, balance,
row_number ()
over (partition by acctno order transno desc) rn
from transtb1
where balance > 0
)
where rn = 1
/
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
