|
|
|
|
| Re: Which aggregate function should use for this problem [message #318567 is a reply to message #318562] |
Wed, 07 May 2008 03:25   |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your example explains almost nothing, so I've written you a nice complex query to generate exactly the numbers you displayed.
select num
,15 - sum(num-1) over (order by num rows between unbounded preceding and current row) tot
from (
select level num from dual connect by level <=5) |
|
|
|
|
|
| Re: Which aggregate function should use for this problem [message #318647 is a reply to message #318567] |
Wed, 07 May 2008 07:55 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I received some more details by PM. Why the OP didn't simply post them, I just don't know:| Quote: | Hi
I want to add the number to the upper one from descending to ascending one
result
1 6 (5+1)
2 5 (3+2)
3 3
|
Rewriting this backwards, I think we're looking for:Value Result
n n
n-1 n+(n-1)
n-2 n+(n-1)+(n-2)
...
3 n+(n-1)+(n-2)+...+3
2 n+(n-1)+(n-2)+...+3+2
1 n+(n-1)+(n-2)+...+3+2+1
A simple mod to the previous query to sum from the current value to the highest should do it:select num
,sum(num) over (order by num rows between current row and unbounded following) tot
from (
select level num from dual connect by level <= x)
|
|
|
|
| Re: Which aggregate function should use for this problem [message #318648 is a reply to message #318647] |
Wed, 07 May 2008 07:58 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| JRowbottom wrote on Wed, 07 May 2008 08:55 | I received some more details by PM. Why the OP didn't simply post them, I just don't know:
|
then I have to hand it to you JRowbottom, your original code was oh so close to the solution based on nothing other than output. Amazing! It's like those Mensa questions.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
