title: Take first, second, third ... last value and selecting rows (Window function with filter and lag)
body: <p>I would like to perform a window function with a filter clause, for example:</p>
  
  <pre><code>LAG("date", 1) FILTER (WHERE type='A') OVER (PARTITION BY id ORDER BY id ASC) AS "A_lag_1"
  </code></pre>
  
  <p>However, Postgres doesn't support this operation but I cant determine how else do it. Details below</p>
  
  <p><strong>Challange</strong></p>
  
  <p>Input <code>tab_A</code>:</p>
  
  <pre><code>+----+------+------+
  | id | type | date |
  +----+------+------+
  |  1 | A    |   30 |
  |  1 | A    |   25 |
  |  1 | A    |   20 |
  |  1 | B    |   29 |
  |  1 | B    |   28 |
  |  1 | B    |   21 |
  |  1 | C    |   24 |
  |  1 | C    |   22 |
  +----+------+------+
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>+----+------+------+---------+---------+---------+---------+---------+---------+
  | id | type | date | A_lag_1 | A_lag_2 | B_lag_1 | B_lag_2 | C_lag_1 | C_lag_2 |
  +----+------+------+---------+---------+---------+---------+---------+---------+
  |  1 | A    |   30 |      25 |      20 |      29 |      28 |      24 |      22 |
  |  1 | A    |   25 |      20 |         |         |         |      24 |      22 |
  |  1 | A    |   20 |         |         |         |         |         |         |
  |  1 | B    |   29 |      25 |      20 |      28 |      21 |      24 |      22 |
  |  1 | B    |   28 |      25 |      20 |      21 |         |      24 |      22 |
  |  1 | B    |   21 |      20 |         |         |         |      24 |      22 |
  |  1 | C    |   24 |      20 |         |      21 |         |      22 |         |
  |  1 | C    |   22 |      20 |         |      21 |         |         |         |
  +----+------+------+---------+---------+---------+---------+---------+---------+
  </code></pre>
  
  <p>In words:</p>
  
  <ul>
  <li>For each row select all rows which occurred before it (see <code>date</code> column)</li>
  <li>Then for each <code>type</code> ('A', 'B', 'C') put the most recent <code>date</code> in <code>A_lag_1</code> and the second the most recent (by date) value in <code>A_lag_2</code> for <code>type</code> 'A', and <code>B_lag_1</code>, <code>B_lag_2</code> for 'B' etc.</li>
  </ul>
  
  <p>The above example is quite simplified, in my real use case there will be a lot more <code>id</code> values, more lag column iterations <code>A_lag_X</code> and types.</p>
  
  <p><strong>Posible solution</strong>
  This challenge seems a perfect fit for a <a href="https://www.postgresql.org/docs/9.6/static/tutorial-window.html" rel="nofollow noreferrer">window function</a> as I want to keep the same number of rows <code>tab_A</code> and append information which is related to the row but in the past.</p>
  
  <p>So constructing it using a window function (<a href="http://sqlfiddle.com/#!17/d566e2/4" rel="nofollow noreferrer">sqlfiddle</a>):</p>
  
  <pre><code>SELECT
    id, type, "date",
    LAG("date", 1) FILTER (WHERE type='A') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "A_lag_1",
    LAG("date", 2) FILTER (WHERE type='A') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "A_lag_2",
    LAG("date", 1) FILTER (WHERE type='B') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "B_lag_1",
    LAG("date", 2) FILTER (WHERE type='B') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "B_lag_2",
    LAG("date", 1) FILTER (WHERE type='C') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "C_lag_1",
    LAG("date", 2) FILTER (WHERE type='C') OVER (PARTITION BY id ORDER BY id ASC, "date" DESC) AS "C_lag_2"
  FROM tab_A
  </code></pre>
  
  <p>However, I get the following error:</p>
  
  <blockquote>
    <p>ERROR: FILTER is not implemented for non-aggregate window functions
    Position: 30</p>
  </blockquote>
  
  <p>Although this error is referenced in the <a href="https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" rel="nofollow noreferrer">documentation</a> I cant determine another way of doing it.</p>
  
  <p>Any help would be much appreciated.</p>
  
  <hr>
  
  <p>Other SO questions:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/a/39599973/1110328">1.</a> This answer relies on using an aggregate function such as <code>max</code>. However, this won't work when trying to retrieve the 2nd last row, 3rd last row etc.</li>
  </ul>