OraFAQ Forum: SQL & PL/SQL » Difference the rows

Home » SQL & PL/SQL » SQL & PL/SQL » Difference the rows (Oracle 11g windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference the rows [message #644921]

Thu, 19 November 2015 18:53

knari007
Messages: 2
Registered: November 2015

Junior Member

I want to add a column to the existing table which should show the difference of salary from previous row with current row.

Report message to a moderator

Re: Difference the rows [message #644922 is a reply to message #644921]

Thu, 19 November 2015 18:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

knari007 wrote on Thu, 19 November 2015 16:53

I want to add a column to the existing table which should show the difference of salary from previous row with current row.

1) rows in a table are like balls in a basket. There is NO inherent order to rows. Which is next ball in a basket?
2) it is a BAD design to store computed values in a static table, because one of the two salary could change which results in wrong computed difference.

Never implement such a flawed "design"

Report message to a moderator

Re: Difference the rows [message #644936 is a reply to message #644921]

Fri, 20 November 2015 06:09

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

knari007 wrote on Fri, 20 November 2015 00:53

I want to add a column to the existing table which should show the difference of salary from previous row with current row.

Create a view instead.

Report message to a moderator

Re: Difference the rows [message #644938 is a reply to message #644922]

Fri, 20 November 2015 07:29

knari007
Messages: 2
Registered: November 2015

Junior Member

table emp
empid salary
101 1000
102 2000
103 2500
104 3300
105 1000
106 1500

I want result set as

empid salary diff_sal
101 1000 NUll
102 2000 1000
103 2500 500
104 3300 800
105 1000 -2300
106 1500 500

Report message to a moderator

Re: Difference the rows [message #644939 is a reply to message #644938]

Fri, 20 November 2015 07:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Difference the rows [message #644941 is a reply to message #644939]

Fri, 20 November 2015 07:49

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use lag

Report message to a moderator

Re: Difference the rows [message #644949 is a reply to message #644941]

Fri, 20 November 2015 09:08

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Bad idea in general. Storing data derived from other row data will not work in multi-session table data modification without serialization which is not performing and not scalable.

SY.

Report message to a moderator

Previous Topic:	Basic sorting concept
Next Topic:	SYS_GUID FUNCTION RETURNING SAME VALUE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 08 23:19:29 CDT 2026