CODEWITHSUNDEEP
oracle-databaseoracle-analytics
user3299544
user3299544

Reputation: 1

Fetch data from columns using max over another column using Oracle analytic functions

I need to fetch the sum between bal and res columns for each different account (acc) using the max(timestamp), look at this:

  ID    ACC BAL RES TIMESTAMP
  --------------------------
  1 100 70  0   1430238709
  2 101 4   0   1430238710
  3 102 0   0   1430238720
  4 103 3   1   1430238721
  5 100 22  1   1430238731
  6 101 89  0   1430238732
  7 102 101 1   1430238742
  8 103 105 1   1430238753
  9 100 106 0   1430238763
 10 101 100 1   1430238774
 11 102 1   1   1430238784
 12 103 65  0   1430238795

What I need is for MAX(timestamp) <= 1430238763, the sum: bal + res grouped by acc like this:

ACC TOT
-------
100 106
101 89
102 102
103 106

I know how to do it using subqueries, but I would like to try Analytics.

Regards

Upvotes: -2

Views: 45

Answers (2)

Jerry
Jerry

Reputation: 19

your query is not solve without sub query. 

select acc,sum(bal + res) from table_name where timestamp in 
(select acc,max(timestamp) from table_Name group by acc having hax(timestamp)<=1430238763)
Regards.

Upvotes: 0

Younes
Younes

Reputation: 395

How about:

Select * from ( Select t.*, max(TIMESTAMP) over (partition by id) mx from tab t ) where mx=TIMESTAMP;

hth

Upvotes: 0

Related Questions