plsql - Using LAG to Find Previous Value in Oracle -

i'm trying use lag function in oracle find previous registration value donors.

to see data, started query find registrations particular donor:

select registration_id, registration_date registration r r.person_id=52503290 order r.registration_date desc;

then used lag function return previous value along recent value:

select registration_id reg_id, registration_date reg_date, lag(registration_date,1) on (order registration_date) prev_reg_date registration person_id=52503290 order registration_date desc;

and results expected:

so thought should place lag function within main query previous value reason, previous value returns null or no value @ all.

select p.person_id person_id, r.registration_date drive_date, lag(r.registration_date,1) on (order r.registration_date) previous_drive_date, p.abo blood_type, dt.description donation_type person p join registration r on p.person_id = r.person_id , p.first_name <> 'pooled' , p.first_name <> 'import' left outer join drives dr on r.drive_id = dr.drive_id , dr.group_id <> 24999 left outer join branches b on r.branch_id = b.branch_id left outer join donor_group dg on dr.group_id = dg.group_id left outer join donation_type dt on r.donation_type_id = dt.donation_type_id trunc(r.registration_date) = trunc(sysdate)-1 , r.person_id=52503290 order r.registration_date desc;

here result set:

any suggestions on missing here? or why query isn't returning values expected?

based on @alex poole's suggestions, changed query like:

select * ( select        p.person_id person_id,        r.registration_date drive_date,        lag(r.registration_date,1) on (partition p.person_id order r.registration_date) previous_drive_date,        p.abo blood_type,        dt.description donation_type        person p        join registration r on p.person_id = r.person_id , p.first_name <> 'pooled' , p.first_name <> 'import'        left outer join drives dr on r.drive_id = dr.drive_id , dr.group_id <> 24999        left outer join branches b on r.branch_id = b.branch_id        left outer join donor_group dg on dr.group_id = dg.group_id        left outer join donation_type dt on r.donation_type_id = dt.donation_type_id --where r.person_id=52503290 ) trunc(drive_date) = trunc(sysdate)-1 order drive_date desc; 

it takes 85 seconds pull first 30 rows:

my original query (before lag function added) took 2 seconds pull approximately 2100 records. nothing select couple of joins , 1 item in clause.

looking @ record counts, person has 5.5 million records , registration has 9.1 million records.

the lag applied within rows match where clause filter, see previous value if yesterday.

you can apply lag in subquery, , filter in outer query:

select * (     select            p.person_id person_id,            r.registration_date drive_date,            lag(r.registration_date,1) on (order r.registration_date) previous_drive_date,            p.abo blood_type,            dt.description donation_type                person p            join registration r on p.person_id = r.person_id , p.first_name <> 'pooled' , p.first_name <> 'import'            left outer join drives dr on r.drive_id = dr.drive_id , dr.group_id <> 24999            left outer join branches b on r.branch_id = b.branch_id            left outer join donor_group dg on dr.group_id = dg.group_id            left outer join donation_type dt on r.donation_type_id = dt.donation_type_id     r.person_id=52503290 ) trunc(drive_date) = trunc(sysdate)-1 order drive_date desc;