LAG works, LEAD returning 1 within the same statement

Question

I am puzzled over LEAD returning always ID=1, which is not even an ID in the table (ID start at ~18k), instead of a valid ID for the next record. NULL values are where they supposed to be, it's just the rows, which supposed to contain a valid ID. The LAG with the same syntax works as expected, returning correct values. The LEAD doesn't even work (correctly), when I comment out LAG. By the way, I copied the code (and just changed table and column names) from my other script, where it was working fine.

UPDATE PRJ SET 
    PrevJob = SRC.PrevID,  -- << write Previous ID
    NextJob = SRC.NextID   -- << write Next ID
FROM PRJ as PRJ
LEFT JOIN  (
      SELECT 
          ID,  -- << ID for joining to the original record
          LAG(ID)  OVER (PARTITION BY RPCode,PNInt ORDER BY OrderNo) AS PrevID, -- << previous works
          LEAD(ID) OVER (PARTITION BY RPCode,PNInt ORDER BY OrderNo) AS NextID  -- << next returns 1
      FROM PRJ as PRJ2 
      ) as SRC ON SRC.ID = PRJ.ID

Here is a sample of results using SELECT (see the 1 values in the last column):

ID       PNInt       RPCode               OrderNo PrevJob              NextJob
-------- ----------- -------------------- ------- -------------------- -------
18783    53          00005320171113120000 1       NULL                 1
18795    53          00005320171113120000 2       18783                1
18789    53          00005320171113120000 3       18795                NULL
18784    53          00005320171127120000 1       NULL                 1
18796    53          00005320171127120000 2       18784                1
18790    53          00005320171127120000 3       18796                NULL
18785    53          00005320171211120000 1       NULL                 1
18797    53          00005320171211120000 2       18785                1
18791    53          00005320171211120000 3       18797                NULL
18786    53          00005320171225120000 1       NULL                 1
18798    53          00005320171225120000 2       18786                1
18792    53          00005320171225120000 3       18798                NULL
18787    53          00005320180108120000 1       NULL                 1
18799    53          00005320180108120000 2       18787                1
18793    53          00005320180108120000 3       18799                NULL

I fear it might be some dumb typo mistake I am blind to see. Or are there any catches with LAG and LEAD?

1. Please edit your question to include sample data as DDL+DML (create statements and insert into statements). 2. Try to run the `select` without the `update` and see what you get for column `NextID`. 3. Try to change the alias of the table in the update statement to something different then the table name. 4. If all else fails, try to use `lag` with `order by desc` instead of `lead` 5. If that also fails - restart and try again. — Zohar Peled, Nov 20 '17 at 10:34
Add `RPCode` to your sample results. Maybe there is problem. — Rokuto, Nov 20 '17 at 10:37
What data is in the field RPCode? I see all values in PNInt are the same. — Denis Rubashkin, Nov 20 '17 at 10:37
Not sure why you need to do this as a subselect either. You'd be better Using a CTE, and then doing your update on the CTE. That only requires one parse of the table, not two. Zohar is right though, no DDL & Sample data, no answer. — Thom A, Nov 20 '17 at 10:38
In my opinion, your solution should work. Link to [sqlfiddle](http://sqlfiddle.com/#!6/8cd91/1/0). — Rokuto, Nov 20 '17 at 11:17
Did you try to do just SELECT, not the UPDATE? can it be any trigger on your table? — sepupic, Nov 20 '17 at 11:20
Thank you for all the suggestions. It was autocompletion on the NextID in the table scheme, I set it to bit instead of bigint. Before I got to put it into question, Gordon Linoff pointed that out as to be checked things and I found the problem. — Oak_3260548, Nov 20 '17 at 14:14

score 3 · Accepted Answer · answered Nov 20 '17 at 12:07

First, you can write this much more simply as:

UPDATE toupdate
    SET PrevJob = SRC.PrevID,  -- << write Previous ID
        NextJob = SRC.NextID   -- << write Next ID
FROM (SELECT PRJ.*, 
             LAG(ID)  OVER (PARTITION BY RPCode, PNInt ORDER BY OrderNo) AS new_PrevID, -- << previous works
             LEAD(ID) OVER (PARTITION BY RPCode, PNInt ORDER BY OrderNo) AS new_NextID  -- << next returns 1
      FROM PRJ
     ) toupdate;

I cannot explain the behavior that you see. Two things to check:

The type of the id fields.
Any triggers that might be defined on the table.

Oh mother nature... The moment I saw your comment about the type of field I knew I had to make a mistake and there it is, I accidentally set the Next field to bit data type. Probably because of autocompletion (bit instead of bigint). Thank you very much. — Oak_3260548, Nov 20 '17 at 14:12

Paul Maxwell · Answer 2 · 2017-11-20T11:52:50.507

Unable to replicate you issue, see this SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE PRJ
    ([ID] int, [PNInt] int, [RPCode] varchar(20), [OrderNo] int, [PrevJ] varchar(5), [NextJ] varchar(4))
;

INSERT INTO PRJ
    ([ID], [PNInt], [RPCode], [OrderNo], [PrevJ], [NextJ])
VALUES
    (18783, 53, 'O0005320171113120000', 1, NULL, '1'),
    (18795, 53, 'O0005320171113120000', 2, '18783', '1'),
    (18789, 53, 'O0005320171113120000', 3, '18795', NULL),
    (18784, 53, 'O0005320171127120000', 1, NULL, '1'),
    (18796, 53, 'O0005320171127120000', 2, '18784', '1'),
    (18790, 53, 'O0005320171127120000', 3, '18796', NULL),
    (18785, 53, 'O0005320171211120000', 1, NULL, '1'),
    (18797, 53, 'O0005320171211120000', 2, '18785', '1'),
    (18791, 53, 'O0005320171211120000', 3, '18797', NULL),
    (18786, 53, 'O0005320171225120000', 1, NULL, '1'),
    (18798, 53, 'O0005320171225120000', 2, '18786', '1'),
    (18792, 53, 'O0005320171225120000', 3, '18798', NULL),
    (18787, 53, 'O0005320180108120000', 1, NULL, '1'),
    (18799, 53, 'O0005320180108120000', 2, '18787', '1'),
    (18793, 53, 'O0005320180108120000', 3, '18799', NULL)
;

Query 1:

      SELECT 
          ID,  -- << ID for joining to the original record
          LAG(ID)  OVER (PARTITION BY RPCode,PNInt ORDER BY OrderNo) AS PrevID, -- << previous works
          LEAD(ID) OVER (PARTITION BY RPCode,PNInt ORDER BY OrderNo) AS NextID  -- << next returns 1
      FROM PRJ as PRJ2

Results:

|    ID | PrevID | NextID |
|-------|--------|--------|
| 18783 | (null) |  18795 |
| 18795 |  18783 |  18789 |
| 18789 |  18795 | (null) |
| 18784 | (null) |  18796 |
| 18796 |  18784 |  18790 |
| 18790 |  18796 | (null) |
| 18785 | (null) |  18797 |
| 18797 |  18785 |  18791 |
| 18791 |  18797 | (null) |
| 18786 | (null) |  18798 |
| 18798 |  18786 |  18792 |
| 18792 |  18798 | (null) |
| 18787 | (null) |  18799 |
| 18799 |  18787 |  18793 |
| 18793 |  18799 | (null) |

I tried your suggestion, but the update still returns 1... RPCode now provided, I'm sorry for forgetting it in the beginning. — Oak_3260548, Nov 20 '17 at 11:10

LAG works, LEAD returning 1 within the same statement

2 Answers2