2

I have similar problem. Can you anyone give me solution. Below is the table value 

    10    
    20    
    30   
    40

I want output like this

10  10-2     8    
20  20-8    12    
30  30-12   18    
40  40-18   22

Sort by ascending and always substract 2 for the first recrod and use the result for the subsequent records.

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
Bala
  • 21
  • 1
  • I don't think you can do this in one query. But generating a sequence is something programming languages are very good at. – winkbrace Jun 30 '15 at 11:45

2 Answers2

6

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( VAL ) AS
          SELECT 10 FROM DUAL
UNION ALL SELECT 20 FROM DUAL
UNION ALL SELECT 30 FROM DUAL
UNION ALL SELECT 40 FROM DUAL;

Query 1:

WITH Parities AS (
  SELECT VAL,
         MOD( ROW_NUMBER() OVER ( ORDER BY VAL ), 2 ) AS Parity
  FROM   TEST
)
SELECT VAL,
       ABS( SUM( CASE PARITY WHEN 1 THEN -VAL ELSE VAL END ) OVER ( ORDER BY VAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) + 2 ) AS total
FROM   Parities

Results:

| VAL | TOTAL |
|-----|-------|
|  10 |     8 |
|  20 |    12 |
|  30 |    18 |
|  40 |    22 |
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Excellent, simple and powerful logic. Thanks a lot – Bala Jun 30 '15 at 12:23
  • @Bala - if this has answered your question then you can mark the answer as correct to indicate the question is closed. If not, then you can post what other issues there are. – MT0 Jun 30 '15 at 13:08
  • I take my hat off to you! *{ :-) That is impressive stuff! – Boneist Jun 30 '15 at 14:56
0

For completness - basic, recursive solution:

SQLFiddle

with t(val, n) as (
  select val, val-2 from test where val = 10
  union all select test.val, test.val-n from test, t where test.val = t.val + 10 )
select * from t
Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24