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

Thread: retrieving the 2nd most recent effdt

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    retrieving the 2nd most recent effdt

    I am trying to retrieve data from a table that has an effdt field but I want the second highest date not the max(effdt). so for the following table
    addresses
    effdt
    1/2/2006
    3/4/2006
    4/21/2006
    I want to select the data in the row with the date 3/4/2006 because it is the 2nd most recent date

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Code:
    select col1 from (
         select rownum rn, col1 from test_date
         order by 2 desc)
    where rn = 2;
    Last edited by ebrian; 04-27-2006 at 02:58 PM.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Thumbs down


    ebrian's sql will NOT work, notice the second highest date is the 8th rownum:
    Code:
    SQL>   SELECT HIREDATE, ROWNUM RN
      2*   FROM EMP ORDER BY HIREDATE DESC
    dbad1>/
    
    HIREDATE            RN
    ----------- ----------
    23-MAY-1987         11
    19-APR-1987          8
    23-JAN-1982         14
    03-DEC-1981         12
    03-DEC-1981         13
    17-NOV-1981          9
    28-SEP-1981          5
    08-SEP-1981         10
    09-JUN-1981          7
    01-MAY-1981          6
    02-APR-1981          4
    22-FEB-1981          3
    20-FEB-1981          2
    17-DEC-1980          1
    
    14 rows selected.
    Use something like this instead:

    Code:
    SQL> SELECT * FROM (
    2  SELECT JOB, HIREDATE, RANK() OVER (
    3                        PARTITION BY JOB
    4                        ORDER BY HIREDATE DESC) RN
    5    FROM EMP)
    6* WHERE RN = 2;
    
    JOB       HIREDATE            RN
    --------- ----------- ----------
    ANALYST   03-DEC-1981          2
    CLERK     23-JAN-1982          2
    MANAGER   01-MAY-1981          2
    SALESMAN  08-SEP-1981          2
    Or in your case:
    Code:
    SELECT * FROM (
       SELECT EFFDT, RANK() OVER (ORDER BY EFFDT DESC) RN
         FROM EMP)
       WHERE RN = 2;

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Apr 2006
    Posts
    377

    Wink

    Good catch LKBrwn.

    My initial query can be modified as follows to work:

    Code:
    select a1 from (
    	select a.a1, rownum rn
    	from (select a1 from test_date
    		group by a1
    		order by 1 desc NULLS LAST) a
    	where rownum <= 2)
    where rn = 2;
    However, if analytics were used, he may prefer to use DENSE_RANK instead of RANK, because RANK could actually skip 2 if there is a tie for 1.

    Code:
    SELECT * FROM (
       SELECT EFFDT, DENSE_RANK() OVER (ORDER BY EFFDT DESC NULLS LAST) RN
         FROM EMP)
       WHERE RN = 2;
    Also, if the date column can be null, notice the use of NULLS LAST !!
    Last edited by ebrian; 04-27-2006 at 05:29 PM.

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