CODEWITHSUNDEEP
sqlselectgroup-bygoogle-bigquerywindow-functions
Kristina
Kristina

Reputation: 271

How to retrieve data from the previous entry/row on a select statement?

How to retrieve data from the previous entry/row on a select statement?

Here is some data: enter image description here

I want to retrieve facID, read_date, and previous date for each row based on facID.

enter image description here

I think using window function can help here like below code:

select factory_id,read_date from (
    select factory_id,read_date,
    rank() over (partition by factory_id order by read_date) as RN
    from Table order by factory_id,read_date
) a

But not sure how to write a code to retrieve the previous date. Thank you in advance

Upvotes: 1

Views: 52

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10172

Use lag:

select 
  factory_id,
  read_date,
  lag(read_date) over (partition by factory_id order by read_date) as last_read_date
from Table
order by factory_id,read_date

Upvotes: 1

Related Questions