Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two rows
Difference between two rows [message #230607] Thu, 12 April 2007 05:32 Go to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Dear SQl Expert,

I have read your article on "Difference between two rows" and I fear I didn't understand all of it. So I have a question refering to this topic.

My table has the columns, computername, message and timestamp.
The rows contain different computernames and different message-types. Now I have to calculate the difference of time between two rows, where the computername is the same.

I had the idea of grouping the rows referring to the computername, but I have no idea how to calculate the difference between two rows.

Thanks for help!

Dagmar
Re: Difference between two rows [message #230616 is a reply to message #230607] Thu, 12 April 2007 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be nice to help if you'd post a test case, create table and insert statements.

Regards
Michel
Re: Difference between two rows [message #230637 is a reply to message #230607] Thu, 12 April 2007 07:01 Go to previous messageGo to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Dear Michel,

Thanks for your help.

I've attached the testcases.

I need the idea, how find the time difference between two messages that belong to one computer.

Thanks in advance!
Re: Difference between two rows [message #230638 is a reply to message #230637] Thu, 12 April 2007 07:12 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select computername, message, time,
  2         time-lag(time) over (partition by computername order by time) diff
  3  from Computermessages
  4  order by computername, time ;

COMPUTERNAME MESSAGE    TIME                     DIFF
------------ ---------- ------------------------ ------------------------------
R1           M1         12/04/2007 14:06:48.000
             M2         12/04/2007 14:06:51.000  +000000000 00:00:03.000000
             M2         12/04/2007 14:07:29.000  +000000000 00:00:38.000000
             M3         12/04/2007 14:07:41.000  +000000000 00:00:12.000000
R2           M1         12/04/2007 14:06:53.000
             M3         12/04/2007 14:07:00.000  +000000000 00:00:07.000000
             M2         12/04/2007 14:07:01.000  +000000000 00:00:01.000000
             M2         12/04/2007 14:07:08.000  +000000000 00:00:07.000000
R3           M1         12/04/2007 14:07:11.000
             M2         12/04/2007 14:07:17.000  +000000000 00:00:06.000000
             M1         12/04/2007 14:07:22.000  +000000000 00:00:05.000000
             M1         12/04/2007 14:07:22.000  +000000000 00:00:00.000000
             M1         12/04/2007 14:07:24.000  +000000000 00:00:02.000000
             M3         12/04/2007 14:07:52.000  +000000000 00:00:28.000000

14 rows selected.

Tell us if you don't understand something.
You'll find information on LAG function there.

Regards
Michel

[Updated on: Thu, 12 April 2007 07:12]

Report message to a moderator

Previous Topic: Better solution for "merge into .."?
Next Topic: Diff betweeen before insert and after insert in trigger?
Goto Forum:
  


Current Time: Tue Dec 06 04:11:20 CST 2016

Total time taken to generate the page: 0.12125 seconds