How to find second max date?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to find second max date?

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Posts
    52

    Angry

    Hi,

    Can any one help me in finding the second latest date from the table. i.e. in emp table I've 3 rows.
    1. 1st jan, 2002
    2. 2nd Jan, 2002
    3. 3rd Jan, 2002.

    I want to get the second record. Plz. its urgent...

    thanx
    Devang

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from
    (select a.*, rank() over(order by hiredate) as rank_date
    from emp a)
    where rank_date=2

    it's probably semi correct, you probably have to look at dense_rank function, I forgot now since I dont have the docs handy

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    for example:
    Table = Emp
    Column = Hire_Date (Date Type)

    SELECT MAX(Hire_Date - 1) FROM Emp;



    F.


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    eh? what if the second row is 2 days?

  5. #5
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    And:

    VARIABLE getsecond varchar2(8)

    DECLARE
    v_Max date;
    v_Second date;
    BEGIN
    SELECT MAX(HIRE_DATE) INTO v_Max from FF;
    SELECT MAX(HIRE_DATE) into v_Second FROM FF WHERE HIRE_DATE != v_Max;
    :getsecond := to_char(v_second, 'dd/mm/yy');
    END;


    I understood (or misunderstood) he wanted the second
    last date, so if there are two or more second last dates,
    so the date is only one.

    03/02/02
    02/02/02
    02/02/02
    01/02/02

    the second last date is 02/02/02.

    F.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    to get the second min. date use the following query:

    select * from
    (select a.*, dense_rank() over(order by hiredate) as rank_date
    from emp a)
    where rank_date = 2
    and rownum < 2

    to get the second max. date use the following query:
    select * from
    (select a.*, dense_rank() over(order by hiredate desc) as rank_date
    from emp a)
    where rank_date = 2
    and rownum < 2

    hth.
    Cheers!
    OraKid.

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    If you have Oracle without analytic function:

    * to find second max date:
    Code:
    select max(hiredate) from emp
    where hiredate<(select max(hiredate) from emp)
    * to find second min date:
    Code:
    select min(hiredate) from emp
    where hiredate>(select min(hiredate) from emp)

  8. #8
    Join Date
    Sep 2001
    Posts
    52
    First of all thanx..do u have any doc on dense_rank and over. I dont understand how that works?

    Thanx..

    Originally posted by balajiyes
    to get the second min. date use the following query:

    select * from
    (select a.*, dense_rank() over(order by hiredate) as rank_date
    from emp a)
    where rank_date = 2
    and rownum < 2

    to get the second max. date use the following query:
    select * from
    (select a.*, dense_rank() over(order by hiredate desc) as rank_date
    from emp a)
    where rank_date = 2
    and rownum < 2

    hth.

  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    do u have any doc on dense_rank and over
    Here

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