title: Why does LAG return null for all but one row?
body: <p>I’m attempting to fetch rows that are ranked second in a group, along with a value from the previous (first-ranked) row using <code>LAG</code>.</p>
  
  <p>However, applying the condition <code>where place = 2</code> causes the result set to only have a <code>LAG</code> result for the first row.</p>
  
  <p>Including a <code>LEAD</code> column causes the expected result to be shown in the <code>LAG</code> column.</p>
  
  <p>I’m using Oracle 19c and the table I’m working with looks like this:</p>
  
  <pre><code>UKNUM PARTY   UKVOTES
  ----- ------ --------
      1 con        6605
      1 lab       23745
      1 lib        8206
      1 plaid      1859
  </code></pre>
  
  <p>There are at least three rows for each <code>uknum</code>, and all of the columns are non-null.</p>
  
  <p>This is my query, intended to fetch the second-placed rows (by <code>ukvotes</code>) with the <code>ukvotes</code> value for the first-placed rows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
    *
  FROM
    (
      SELECT
        uknum,
        rank() OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS place,
        lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag
      FROM
        ukresults
    )
  WHERE
    place = 2
    AND uknum BETWEEN 1 AND 5;
  </code></pre>
  
  <p>This yields the following results:</p>
  
  <pre><code>     UKNUM      PLACE        LAG
  ---------- ---------- ----------
           1          2      23745
           2          2
           3          2
           4          2
           5          2
  </code></pre>
  
  <p>I would expect that for rows 2-5 there would be a non-null value in the <code>LAG</code> column.</p>
  
  <p>Adding <code>LEAD</code> to the query causes the <code>LAG</code> column to contain the expected results:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
    *
  FROM
    (
      SELECT
        uknum,
        rank() OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS place,
        lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag,
        lead(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lead
      FROM
        ukresults
    )
  WHERE
    place = 2
    AND uknum BETWEEN 1 AND 5;
  </code></pre>
  
  <pre><code>     UKNUM      PLACE        LAG       LEAD
  ---------- ---------- ---------- ----------
           1          2      23745       6605
           2          2      19262       7426
           3          2      15393      10372
           4          2      31288       6070
           5          2      24148      11599
  </code></pre>
  
  <p>I’m confused as to why the first query does not produce the expected results, with a <code>LAG</code> value for all of the rows.</p>
  
  <p><a href="https://dbfiddle.uk/?rdbms=oracle_18&amp;fiddle=b30e800c9978dc0375e5d3dd7b46d565" rel="nofollow noreferrer">Here’s a DBFiddle with the data and the queries loaded.</a></p>
  
  <p>Update: here are the query plans from Oracle 19c, first for the query using <code>LAG</code> only, then one for <code>LEAD</code> by itself or <code>LAG</code> and <code>LEAD</code> together (plan is the same for either query containing <code>LEAD</code>).</p>
  
  <p><code>LAG</code> only plan:</p>
  
  <pre><code>--------------------------------------------------------------------------------------
  | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT         |           |   197 |  7683 |     5  (20)| 00:00:01 |
  |*  1 |  VIEW                    |           |   197 |  7683 |     5  (20)| 00:00:01 |
  |*  2 |   WINDOW SORT PUSHED RANK|           |   197 |  1773 |     5  (20)| 00:00:01 |
  |   3 |    TABLE ACCESS CLUSTER  | UKRESULTS |   197 |  1773 |     4   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN     | UKNUMX    |     1 |       |     2   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("PLACE"=2)
     2 - filter(RANK() OVER ( PARTITION BY "UKNUM" ORDER BY
                INTERNAL_FUNCTION("UKVOTES") DESC )&lt;=2)
     4 - access("UKNUM"&lt;=50)
  </code></pre>
  
  <p><code>LEAD</code> only / <code>LAG</code> and <code>LEAD</code> plan:</p>
  
  <pre><code>------------------------------------------------------------------------------------
  | Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT       |           |   197 |  7683 |     5  (20)| 00:00:01 |
  |*  1 |  VIEW                  |           |   197 |  7683 |     5  (20)| 00:00:01 |
  |   2 |   WINDOW SORT          |           |   197 |  1773 |     5  (20)| 00:00:01 |
  |   3 |    TABLE ACCESS CLUSTER| UKRESULTS |   197 |  1773 |     4   (0)| 00:00:01 |
  |*  4 |     INDEX RANGE SCAN   | UKNUMX    |     1 |       |     2   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("PLACE"=2)
     4 - access("UKNUM"&lt;=50)
  </code></pre>