title: How to get in SQL oracle nth_value order by one variable with windowing_clause depending on another variable?
body: <p>I want to get max, second and third value of column in few lengths of window based on time.</p>
  <p>For example, I've got data like this</p>
  <pre><code>X   | DATE    |   Y  
  --------------------
  N1  | 2019-01 |  100
  N1  | 2019-02 | (null)
  N1  | 2019-03 |   30
  N1  | 2019-04 |   40
  N1  | 2019-05 |   70
  N2  | 2019-01 |   80
  N2  | 2019-02 |   60
  N2  | 2019-03 |   40
  N2  | 2019-04 |   50
  N2  | 2019-05 |   30
  </code></pre>
  <p>I wish to get maximum and second max value of Y over last three months, like this:</p>
  <pre><code>X   | DATE    | MAX(Y) | SEC(Y)  
  -------------------------------
  N1  | 2019-01 |  100   | (null)
  N1  | 2019-02 |  100   | (null)
  N1  | 2019-03 |  100   |   30
  N1  | 2019-04 |   40   |   30
  N1  | 2019-05 |   70   |   40
  N2  | 2019-01 |   80   | (null) 
  N2  | 2019-02 |   80   |   60
  N2  | 2019-03 |   80   |   60
  N2  | 2019-04 |   60   |   50
  N2  | 2019-05 |   50   |   40
  </code></pre>
  <p>Maximum I can get by</p>
  <pre><code>select distinct
  X, DATE, 
  max(y) over (partition by X, DATE order by DATE RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW)
  </code></pre>
  <p>What I should to do to get this second (or another nth_value) if the data are in first ordering by DATE value? Adding Y after order by clause doesn't work...
  Or, maybe I should reorganize the input data?</p>