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

Question

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

Younes · Accepted Answer · 2015-04-28 17:59:57Z

0

How about:

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

hth

answered Apr 28, 2015 at 17:59

Younes

3955 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerry · Accepted Answer · 2015-04-29 12:50:14Z

0

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.

answered Apr 29, 2015 at 12:50

Jerry

193 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related