title: Select a MAX when using a window function that orders with nulls first in Postgres
body: <p>I have a query that I'm attempting to optimize and running into some unexpected behavior.</p>
  
  <p>For example, take a table like this:</p>
  
  <pre><code>CREATE TABLE objects
      (id int, external_id int, timestamp timestamp);
  
  INSERT INTO objects
      (id, external_id, timestamp)
  VALUES
      (1, 1, '2019-08-16 12:00:00'),
      (2, 1, NULL),
      (3, 2, '2019-08-16 12:00:00'),
      (4, 2, NULL);
  </code></pre>
  
  <p>I use a query as so to partition the <code>objects</code> by their <code>external_id</code> and then select the maximum value for <code>timestamp</code> across the partition:</p>
  
  <pre><code>SELECT *, 
  max(timestamp) OVER (PARTITION BY external_id) as max_timestamp, 
  row_number() OVER (PARTITION BY external_id ORDER BY timestamp ASC NULLS FIRST, id ASC) 
  FROM objects;
  </code></pre>
  
  <p>This produces the following (which is what I'm looking for):</p>
  
  <p><strong>[Results]</strong>:</p>
  
  <pre><code>| id | external_id |            timestamp |        max_timestamp | row_number |
  |----|-------------|----------------------|----------------------|------------|
  |  2 |           1 |               (null) | 2019-08-16T12:00:00Z |          1 |
  |  1 |           1 | 2019-08-16T12:00:00Z | 2019-08-16T12:00:00Z |          2 |
  |  4 |           2 |               (null) | 2019-08-16T12:00:00Z |          1 |
  |  3 |           2 | 2019-08-16T12:00:00Z | 2019-08-16T12:00:00Z |          2 |
  </code></pre>
  
  <p>I want to remove the multiple window functions in favor of a single one. For example:</p>
  
  <pre><code>SELECT *, 
  max(timestamp) OVER w as max_timestamp, 
  row_number() OVER w
  FROM objects
  WINDOW w AS (PARTITION BY external_id ORDER BY timestamp ASC NULLS FIRST, id ASC);
  </code></pre>
  
  <p>However, doing this produces a different result with the <code>max_timestamp</code> set to NULL for half the results:</p>
  
  <p><strong>[Results]</strong>:</p>
  
  <pre><code>| id | external_id |            timestamp |        max_timestamp | row_number |
  |----|-------------|----------------------|----------------------|------------|
  |  2 |           1 |               (null) |               (null) |          1 |
  |  1 |           1 | 2019-08-16T12:00:00Z | 2019-08-16T12:00:00Z |          2 |
  |  4 |           2 |               (null) |               (null) |          1 |
  |  3 |           2 | 2019-08-16T12:00:00Z | 2019-08-16T12:00:00Z |          2 |
  </code></pre>
  
  <p>Why would introducing a sort order to the window function have any effect on the return of <code>max</code>?</p>
  
  <p><a href="http://sqlfiddle.com/#!17/1fcc4/4" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/1fcc4/4</a></p>