DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: last but one row

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    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

  2. #2
    Join Date
    Apr 2008
    Posts
    6

    need table data

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

  3. #3
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    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
  •  


Click Here to Expand Forum to Full Width