I need to get the previous id from a table, but I want to limit that previous record to a given id which is id_sucursal (branch), because if I do not do this, the first row (of id_sucursal) will get the previous_id (the lag) from another sucursal. I know I can order over id_sucursal and works fine but I want to keep it on the same id_sucursal context, this is the select statement, is so simple and does not needs more.
Select
-- current id
t.id,
-- when i order everything is ok but on the first row
-- I get the id from another sucursal
lag(id) OVER (ORDER BY t.id_sucursal) prev_id,
-- here sucursal may be different
t.id_sucursal
From sucursal_info
It would be great to do something like:
Select
t.id,
lag(id) OVER (ORDER BY t.id_sucursal) (WHERE id_sucursal = t.id_sucursal) prev_id,
--
t.id_sucursal
From sucursal_info
Of course there are more rows involved but I just brought needed ones to explain this.
Looks like you need to add partition by
clause in LAG
.
....
lag(id) OVER (parition by id_sucursal ORDER BY id_sucursal) prev_id.
.....
Also I don't see you setting alias for the table as t
but you are referring to columns as t.column_name
. Hence I removed it