-
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
-
need table data
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks PAVB !! that worked
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|