title: How to select only the second max date from a table
body: <p>I have a table where the delivery date and order number are stored.</p>
  
  <p>Here i was able to get the order with the max delivery date. </p>
  
  <pre><code>SELECT DISTINCT D.ORDER_NO
  FROM DELIVERY D
  WHERE D.CUSTOMER_NO =112 AND D.DELIVERY_DATE = (SELECT  MAX(D1.DELIVERY_DATE) FROM DELIVERY D1
  WHERE D1.CUSTOMER_NO = 112 );
  </code></pre>
  
  <p>Here a single customer may have multiple orders.</p>
  
  <p>Now what i want is to get only the second max date.</p>
  
  <p>By using the above query I was able to get the list of data other than the max delivery date by changing the <code>=</code> to <code>&lt;</code> and adding <code>ORDER BY</code> in the subquery.</p>
  
  <p>But its an entire list but i want only the second max date.</p>
  
  <p>Someone pls tell me how I can get only the second max date.</p>
  
  <p>Note: I have tried using <code>ROWNUM&lt;=1</code> but i am getting wrong date</p>