I want to get the previous row from a table

[divya_rad]

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

[bhaski]

can't understand what you need can explain it properly?

[jamesravid]

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.

[prem1977]

You can use Lead Analytical function to get Previous or next row.

[debasisdas]

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;

[divya_rad]

can't understand what you need can explain it properly?

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...

[divya_rad]

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.

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...

[jamesravid]

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).