Search code examples
sqlsql-serversql-server-2012lag

Sql Server Lag in same context?


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.

Here is another example in smms: enter image description here


Solution

  • 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