Home » SQL & PL/SQL » SQL & PL/SQL » lag function (Oracle 8.1.7, windows)
lag function [message #345341] Wed, 03 September 2008 02:42 Go to next message
saudalnasr
Messages: 7
Registered: August 2008
Junior Member
hello all users,

i have a query, i am using the emp and dept no from scott schema.


select deptno,ename,sal,hiredate,
nvl(lag(hiredate) over (order by deptno,hiredate desc),sysdate) prev_date  from emp
where sal < 2500
 order by deptno


the output i get is as follow

Deptno  Ename   sal    hiredate   prev_date
10      miller  1300   23-jan-82  02-sep-08
10      clark   2450   09-jun-81  23-jan-82
20      Adams   1100   23-may-87  09-jun-81
20      Smith   800    17-dec-80  23-may-87
30      james   950    03-dec-81  17-dec-80
30      Martin  1250   28-sep-81  03-dec-81
30      Turner  1500   08-sep-81  28-sep-81
30      Ward    1250   22-Feb-81  08-sep-81
30      Allen   1600   20-feb-81  22-feb-81


Prev_date coloumn is a derived coloumn using Lag function
now my question is

when the dept no changes the from 30 to 20 the prevdate should by default should be sysdate, not the hiredate from dept no 20.

i will make it more clear the desired result i need is

Deptno  Ename   sal    hiredate   prev_date
10      miller  1300   23-jan-82  [B]02-sep-08[/B]
10      clark   2450   09-jun-81  23-jan-82
20      Adams   1100   23-may-87  [B]02-sep-08[/B]
20      Smith   800    17-dec-80  23-may-87
30      james   950    03-dec-81  [B]02-sep-08[/B]
30      Martin  1250   28-sep-81  03-dec-81
30      Turner  1500   08-sep-81  28-sep-81
30      Ward    1250   22-Feb-81  08-sep-81
30      Allen   1600   20-feb-81  22-feb-81


any help will be highly appreciated.




Re: lag function [message #345363 is a reply to message #345341] Wed, 03 September 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You then have to partition by department.

Regards
Michel
Re: lag function [message #345366 is a reply to message #345363] Wed, 03 September 2008 04:16 Go to previous messageGo to next message
saudalnasr
Messages: 7
Registered: August 2008
Junior Member
Mr Michel Cadot.

please to here from u quickly, can u briefly give me some example how to go with it,
it will be of good help
Re: lag function [message #345367 is a reply to message #345366] Wed, 03 September 2008 04:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
How did you find out how to use the lag function? I'm gong to assume that you looked up the syntax in the manual. Well, partion by is a simple extension to the syntax that you have already learned. Go and check the documentation on how to extend your syntax with partition by.
Re: lag function [message #345375 is a reply to message #345367] Wed, 03 September 2008 04:54 Go to previous messageGo to next message
saudalnasr
Messages: 7
Registered: August 2008
Junior Member
HEY PABLOLEE,

good to here from u, i understand that u want me to get this by my self and i believe in the same cause self learning is good.

ok however the reason i asked michel to help me cause i didnt know what is partitioned by is it a function?
some utility?
some database concept? like partitioning table.

its ok i am ready to search by my self and get the answer i just want some specific thing what i should i search for.

if u can help me on even this it will be a pleasure.

Saud

Re: lag function [message #345377 is a reply to message #345375] Wed, 03 September 2008 04:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi Saud,
as I already said in my post, partition by is simply an extension to the lag function. Go back to the documentation and check out the syntax for the lag function. In there you will see that part of the syntax include the partition by clause.

[Updated on: Wed, 03 September 2008 04:56]

Report message to a moderator

Re: lag function [message #345379 is a reply to message #345375] Wed, 03 September 2008 04:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's part of the syntax for Analytic Functions

Documentation
Re: lag function [message #345382 is a reply to message #345375] Wed, 03 September 2008 05:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Just a polite note, Please don't use IM language like (U, ur, v, r) in public forum. Also don't use capital letters. It will be understood as shouting.

Regards

Raj
Re: lag function [message #345403 is a reply to message #345382] Wed, 03 September 2008 07:28 Go to previous message
saudalnasr
Messages: 7
Registered: August 2008
Junior Member
hey all,

thanks for your help.

the query is solved.

i didnt knew i was so close to solving my problem,

once again thanks to every one.

Saud
Previous Topic: Validation of column values of records in PL/SQL table
Next Topic: records within a quarter
Goto Forum:
  


Current Time: Fri Dec 02 18:32:21 CST 2016

Total time taken to generate the page: 0.24526 seconds