last but one row

i was trying to get the last but one row for every order but unsuccessful.I tried the below.it works for one order_id perfectly but not for every order

SELECT *
FROM (
SELECT order_id, ordered_date,
RANK() OVER (ORDER BY ordered_date DESC) ORANK
FROM orders where order_id =56789)
WHERE orank = 2;

any help is much appreciated

Thanks

can you provide me the table data in which case it is failing???

order_id, orderd_date,order revision
======================================
55761 ,'10-MAR-05',1
55761,'11-MAR-05',2
55761,'12-MAR-05',3

55762 ,'12-MAR-05',1
55762,'13-MAR-05',2
55762,'14-MAR-05',3

55763 ,'15-MAR-05',1
55763,'15-MAR-05',2
55763,'16-MAR-05',3
55763,'17-MAR-05',4
====================================
i am looking for results like this
order_id, orderd_date,order revision
55761,'11-MAR-05',2
55762,'13-MAR-05',2
55763,'16-MAR-05',3
======================================
when i run thsi query for one job i got results

SELECT *
FROM (
SELECT order_id, ordered_date,orderrevision,
RANK() OVER (ORDER BY ordered_date,orderrevision DESC) ORANK
FROM orders where order_id =55761)
WHERE orank = 2;

order_id, orderd_date,order revision
55761,'11-MAR-05',2
========================================
but when i do this
SELECT *
FROM (
SELECT order_id, ordered_date,
RANK() OVER (ORDER BY ordered_date,orderrevision DESC) ORANK
FROM orders )
WHERE orank = 2;

thsi doesn't return for each instead returns last but last row in table

Code:

---

SELECT *
FROM
(
SELECT  order_id,
        ordered_date,
        RANK() OVER (PARTITION BY ORDER_ID ORDER BY ordered_date DESC,orderrevision DESC) ORANK
FROM    orders
)
WHERE orank = 2
;
select *
from  orders
order by order_id DESC,ordered_date DESC,orderrevision DESC
; 
               
ORDER_ID        ORDERED_DATE                    ORANK
55761            03/11/2005 12:00:00.000 AM        2
55762            03/13/2005 12:00:00.000 AM        2
55763            03/16/2005 12:00:00.000 AM        2
               
ORDER_ID        ORDERED_DATE                    ORDERREVISION
55763            03/17/2005 12:00:00.000 AM        4
55763            03/16/2005 12:00:00.000 AM        3
55763            03/15/2005 12:00:00.000 AM        2
55763            03/15/2005 12:00:00.000 AM        1
55762            03/14/2005 12:00:00.000 AM        3
55762            03/13/2005 12:00:00.000 AM        2
55762            03/12/2005 12:00:00.000 AM        1
55761            03/12/2005 12:00:00.000 AM        3
55761            03/11/2005 12:00:00.000 AM        2
55761            03/10/2005 12:00:00.000 AM        1

---

Thanks PAVB !! that worked