Re: a sql question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 06 Mar 2007 09:31:45 +0100
Message-ID: <45ED26F1.7050103@gmail.com>

David Portas schrieb:

> On 5 Mar, 22:36, "z..._at_hotmail.com" <z..._at_hotmail.com> wrote:

>> Hi,
>>
>> Oracle 10g.
>> I have a table with data like this:
>>
>> id val
>> 1 100
>> 2 101
>> 3 200
>> 4 300
>>
>> and I want a query which displays the result of "val" minus the
>> "previous val", order by id, something like:
>>
>> id computed_value
>> 2 1
>> 3 99
>> 4 100
>>
>> How do I accomplish that using sql (not pl/sql)?
>>
>> TIA
>> Guang

> 
> SELECT t1.id, t1.val-t2.val AS computed_value
> FROM tbl AS t1, tbl AS t2
> WHERE t1.id-1 = t2.id;
> 
> --
> David Portas
> 

This is not quite Oracle syntax ( FROM tbl AS t1 ), but it is probably a typo. The main flaw by this approach is - it requires access to table TBL two times and it may not work if ID is a sequence generated value ( i.e, may contain gaps). As the question was formulated, it sounds as typical task for analytics .

SQL> with t as(

   2 select 1 id,100 val from dual union all

   3  select 2,101 from dual union all
   4  select 3,200 from dual union all
   5  select 4,300 from dual)

   6 select id,new_val as computed_value from (    7 SELECT id, val, val - lag(val) over(order by id) new_val    8 from t)
   9 where new_val is not null
  10 /

         ID COMPUTED_VALUE
---------- --------------

          2              1
          3             99
          4            100

Best regards

Maxim Received on Tue Mar 06 2007 - 02:31:45 CST