How to use LEAD and LAG using a condition

Question

My question is, if I can use LAG and LEAD, using a condition in the query, for example:

I have the spine cep..

I want to take the value of the next row, and the value of the previous row of that same column.. I’m working with c# and SQL server 2012.. but making a list in c# with all results is not feasible, it’s almost 600 thousand records.

I did a test with LAG and LEAD

SELECT
LAG(cid_nucep) OVER (ORDER BY cid_nomec) CidadeAnte,
cid_nucep AS CidadeAtual,
LEAD(cid_nucep) OVER (ORDER BY cid_nomec) ProxCidade
FROM bcadcida
GO

The return was close to what I need:

NULL 87365000 68912350 87365000 68912350 48320000 68912350 48320000 74423970

But if I use a condition:

SELECT
LAG(cid_nucep) OVER (ORDER BY cid_nomec) CidadeAnte,
cid_nucep AS CidadeAtual,
LEAD(cid_nucep) OVER (ORDER BY cid_nomec) ProxCidade
FROM bcadcida where cid_nucep = 77777777
GO

The result is flawed..

NULL 77777777 NULL

How can I take for the cid_nucep value of the previous line and the next one?

Answer 1

I assume your table looks like this (in this order):

cid_nomec | cid_nucep 
(nome)      87365000 
(nome)      68912350 
(nome)      48320000 
(nome)      74423970

Somewhere there’s this zip code 77777777:

cid_nomec | cid_nucep 
...
(nome)      77777777
...

The problem is that you have restricted the query to a result that brings only one line, therefore LEAD and LAG will come null because there are no previous or later elements.

If I understand what you want, you want to pass a zip code to the query and get the values of LEAD and LAG of it. This is done as follows:

SELECT CidadeAnte, CidadeAtual, ProxCidade from
    (SELECT
        LAG(cid_nucep) OVER (ORDER BY cid_nomec) CidadeAnte,
        cid_nucep AS CidadeAtual,
        LEAD(cid_nucep) OVER (ORDER BY cid_nomec) ProxCidade
        FROM bcadcida
    )
WHERE CidadeAtual = 77777777

GO