Home » SQL & PL/SQL » SQL & PL/SQL » count difference between tuples
count difference between tuples [message #227980] Fri, 30 March 2007 09:18 Go to next message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
Hi

i have a table like

1 1.1.2007
2 2.1.2007
5 3.1.2007
2 4.1.2007
8 5.1.2007
9 6.1.2007

and i want make one SQL-Statment, wich show me

NULL or 0 1.1.2007
1 2.1.2007
3 3.1.2007
-3 4.1.2007
6 5.1.2007
1 6.1.2007
Re: count difference between tuples [message #227982 is a reply to message #227980] Fri, 30 March 2007 09:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Analytics are great for this:
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  ( col1 number
  3  , col2 date
  4  );

Table created.

SQL> insert into faq values (8, to_date('05-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> insert into faq values (9, to_date('06-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> insert into faq values (1, to_date('01-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> insert into faq values (2, to_date('02-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> insert into faq values (5, to_date('03-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> insert into faq values (2, to_date('04-01-2007', 'dd-mm-yyyy'));

1 row created.

SQL> 
SQL> select col1 - lag(col1) over (order by col2)
  2  ,	    col2
  3  from   faq
  4  order  by col2
  5  ;

COL1-LAG(COL1)OVER(ORDERBYCOL2) COL2
------------------------------- ---------
                                01-JAN-07
                              1 02-JAN-07
                              3 03-JAN-07
                             -3 04-JAN-07
                              6 05-JAN-07
                              1 06-JAN-07

6 rows selected.


[Edit: added an order by]

[Updated on: Fri, 30 March 2007 09:26]

Report message to a moderator

Re: count difference between tuples [message #227983 is a reply to message #227980] Fri, 30 March 2007 09:28 Go to previous message
ddaannyy
Messages: 13
Registered: March 2007
Junior Member
wow that is going very fast

many thanks
Previous Topic: DBMS_SCHEDULER
Next Topic: Finding missing sequences
Goto Forum:
  


Current Time: Sat Dec 10 02:48:50 CST 2016

Total time taken to generate the page: 0.07804 seconds