How to select only the second max date from a table

Question

I have a table where the delivery date and order number are stored.

Here i was able to get the order with the max delivery date.

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 );

Here a single customer may have multiple orders.

Now what i want is to get only the second max date.

By using the above query I was able to get the list of data other than the max delivery date by changing the = to < and adding ORDER BY in the subquery.

But its an entire list but i want only the second max date.

Someone pls tell me how I can get only the second max date.

Note: I have tried using ROWNUM<=1 but i am getting wrong date

Solution 1

SELECT D.ORDER_NO
FROM
(
SELECT DISTINCT D.ORDER_NO,D.DELIVERY_DATE,ROW_NUMBER()OVER(order by D.DELIVERY_DATE desc) RowNo
FROM DELIVERY D
WHERE D.CUSTOMER_NO =1128158 
ORDER BY D.DELIVERY_DATE DESC)
t WHERE t.RowNo = 2;

Solution 2

Assuming you are using Oracle:

SELECT *
FROM
(
    SELECT t.*, rownum rnum
    FROM
    (
        SELECT DISTINCT D.ORDER_NO
        FROM DELIVERY D
        WHERE D.CUSTOMER_NO = 112
        ORDER BY D.DELIVERY_DATE DESC
    ) t
    WHERE rownum <= 2
)
WHERE rnum >= 2

Solution 3

SELECT * FROM(
SELECT DISTINCT D.ORDER_NO, ROW_NUMBER()OVER(order by D.DELIVERY_DATE desc) RowNo
FROM DELIVERY D
WHERE D.CUSTOMER_NO =112
) t where t.RowNo = 2

Solution 4

You can try this query:

SELECT DISTINCT ORDER_NO FROM DELIVERY WHERE CUSTOMER_NO=112 AND DELIVERY_DATE =
(SELECT MAX(DELIVERY_DATE) FROM DELIVERY WHERE CUSTOMER_NO=112 AND 
DELIVERY_DATE<(SELECT MAX(DELIVERY_DATE) FROM DELIVERY AND CUSTOMER_NO=112));

The sub query will return second max delivery date which will give order_no of second max date.

Solution 5

Select Second max date From table

 SELECT MAX(date) FROM tbl_date WHERE date NOT IN (SELECT MAX(date) FROM tbl_date )
        OR
        SELECT DISTINCT date FROM tbl_date  ORDER BY date DESC LIMIT 1,1;
        OR
        SELECT MAX(date) FROM ( SELECT date FROM tbl_date  MINUS SELECT MAX(date) FROM tbl_date)

View more solutions