I want to get the previous row from a table
How do i get the previous row from a table? The table has key fields as emplid (employee ID), Empl_rcd(employement record),effdt and effseq. I can pass all these values from the current row. But under one effdt there can be more than one effseq. A sample of the data
Emplid Empl_rcd Effdt Effseq
TT20 15 18/07/07 0
TT20 15 17/07/07 2
TT20 15 17/07/07 1
TT20 15 17/07/07 0
Re: I want to get the previous row from a table
can't understand what you need can explain it properly?
Re: I want to get the previous row from a table
the eff_seq of the previous row must be one less than current rows eff_sql and all other key-column values are going to be same.
as bhaski said if you explain ur problem bit clearly then we would be able to provide you a solution.
Re: I want to get the previous row from a table
You can use Lead Analytical function to get Previous or next row.
Re: I want to get the previous row from a table
Lag / lead:-
==========
These functions allows rows to be referenced by their position relative to the current row.these are useful for compairing one row of a result set with another row of the same result set.
Sample ex1
----------
select hiredate,sum(sal) monthlysal,lag(sum(sal),1) over( order by hiredate) previous_month_sal from emp group by hiredate order by hiredate;
sample ex2
-----------
select hiredate,sum(sal) monthlysal, lag(sum(sal),1) over( order by hiredate) previous_month_sal, lead(sum(sal),1) over( order by hiredate) next_month_sal from emp group by hiredate order by hiredate;
Re: I want to get the previous row from a table
[QUOTE=bhaski;15622]can't understand what you need can explain it properly?[/QUOTE]
Thanks for the reply. :)
Suppose I am passing the values
emplid : TT20 , effdt : 18/07/2007 and effseq = 0 then i should get
the row corresponding to Emplid :TT20 effdt : 17/07/2007 and effseq = 2
Again , if i pass Emplid :TT20 effdt : 17/07/2007 and effseq = 2,i should get
Emplid :TT20 effdt : 17/07/2007 and effseq = 1 and so on...
Re: I want to get the previous row from a table
[QUOTE=jamesravid;15625]the eff_seq of the previous row must be one less than current rows eff_sql and all other key-column values are going to be same.
as bhaski said if you explain ur problem bit clearly then we would be able to provide you a solution.[/QUOTE]
Thanks for the reply. :)
Suppose I am passing the values
emplid : TT20 , effdt : 18/07/2007 and effseq = 0 then i should get
the row corresponding to Emplid :TT20 effdt : 17/07/2007 and effseq = 2
Again , if i pass Emplid :TT20 effdt : 17/07/2007 and effseq = 2,i should get
Emplid :TT20 effdt : 17/07/2007 and effseq = 1 and so on...
Re: I want to get the previous row from a table
Try this query,
select Emplid, Empl_rcd, Effdt, Effseq
from your_table
where emplid = :emplid
and effdt = decode(:Effseq,0, :effdt -1, :effdt)
and effseq = decode(:effseq,0, select max(effseq)
from your_table
where effdt = :effdt-1
,:effseq -1);
:effseq,:effdat and :emplid are the bind variables (your input parameters).