How to use LEAD and LAG using a condition

Question

How to use LEAD and LAG using a condition

Navigation

#1 by (2 votes)

2

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

I have the zip column ..

I want to get the value of the next line, and the value of the previous line in that same column. I'm working with C # and SQL Server 2012 .. but making a list in C # with all results is impractical, it's almost 600,000 records.

I tested 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

Return was close to what I need:

NULL 87365000 68912350 87365000 68912350 48320000 68912350 48320000 74423970

However, 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 get the value of cid_nucep, the previous line, and the next line?

sql sql-server c# tsql sql-server-2012

asked by anonymous 29.04.2014 / 18:38

1 answer

Use the Excel Application object within Access outputStream writing a byteArray - XSS validation

score 2 · Accepted Answer

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

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

Somewhere there is this zip code 77777777 :

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

The problem is that you have restricted the query to a result that has one row only, so LEAD and LAG will be null because there are no previous or subsequent 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 . 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