Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate and select in same query?
Aggregate and select in same query? [message #306303] Thu, 13 March 2008 10:47 Go to next message
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 #306309 is a reply to message #306303] Thu, 13 March 2008 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and take the first row.

Regards
Michel
Re: Aggregate and select in same query? [message #306363 is a reply to message #306309] Thu, 13 March 2008 21:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT   accntno, MAX(transno), MAX(balance) KEEP (DENSE_RANK LAST ORDER BY transno)
FROM     transtbl
WHERE    balanc > 0
GROUP BY accntno;


Ross Leishman
Re: Aggregate and select in same query? [message #306578 is a reply to message #306363] Fri, 14 March 2008 14:40 Go to previous messageGo to next message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
rleishman wrote on Fri, 14 March 2008 03:50
SELECT   accntno, MAX(transno), MAX(balance) KEEP (DENSE_RANK LAST ORDER BY transno)
FROM     transtbl
WHERE    balanc > 0
GROUP BY accntno;

Thank you very much - this was really helpful! It even made it possible to solve my problem in the thread Joining with huge tables? so I can now run all the queries in less than 15 minutes. Smile
Re: Aggregate and select in same query? [message #306586 is a reply to message #306578] Fri, 14 March 2008 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also try the other way.
Depending on your environment the one or the other is better.

Regards
Michel
Re: Aggregate and select in same query? [message #306589 is a reply to message #306586] Fri, 14 March 2008 15:58 Go to previous messageGo to next message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
Michel Cadot wrote on Fri, 14 March 2008 21:24
Also try the other way.
Depending on your environment the one or the other is better.
Sorry, what is the other way in this case?
Re: Aggregate and select in same query? [message #306616 is a reply to message #306589] Sat, 15 March 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 13 March 2008 17:05
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and take the first row.

Regards
Michel


Re: Aggregate and select in same query? [message #306617 is a reply to message #306589] Sat, 15 March 2008 02:09 Go to previous message
Michel Cadot
Messages: 64152
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
Previous Topic: Is It Possible To Read an data from tabel in which Column Defined as Type?
Next Topic: How to update only year in date?
Goto Forum:
  


Current Time: Sat Dec 10 04:51:37 CST 2016

Total time taken to generate the page: 0.05755 seconds