Re: How to do this trick ?

Norazman Abu Sahir <norazman_at_ti.com> wrote in message news:38069C0B.80DF880E_at_ti.com...
> Hi,
> I have a table with one colum. When I do a select below are the data.
>
> SQL> SELECT MYNUMBER FROM TESTING;
>
> MYNUMBER
> ----------
> 1
> 3
> 5
> 10
>
> What I would like to do is how to display a different betwen a row value
> with a previous row value.
> Something like this:
>
> MYNUMBER DIFF
> ---------- -------
> 1 0
> 3 2
> 5 2
> 10 5
>
> Have any idea?
> TIA
What is your logical order of records?
If you mean the order Oracle reads from disk, this is the answer:

SQL> create table testing (mynumber number);

Table created.

SQL> insert into testing values (1);

1 row created.

SQL> insert into testing values (3);

1 row created.

SQL> insert into testing values (5);

1 row created.

SQL> insert into testing values (10);

1 row created.

SQL> select a.mynumber, nvl(a.mynumber-b.mynumber, 0) as diff   2 from (select mynumber, rownum as row_num from testing) a,   3 (select mynumber, rownum as row_num from testing) b   4 where a.row_num=b.row_num(+)+1;

 MYNUMBER DIFF
--------- ---------

        1         0
        3         2
        5         2
       10         5

If you mean the order is the column MYNUMBER, this is the answer:

SQL> select a.mynumber, nvl(a.mynumber-max(b.mynumber), 0) as diff   2 from testing a, testing b
  3 where a.mynumber>b.mynumber(+)
  4 group by a.mynumber;

 MYNUMBER DIFF
--------- ---------

        1         0
        3         2
        5         2
       10         5