Home » SQL & PL/SQL » SQL & PL/SQL » max(date) and previous max(date) (sql*plus, 10.1.0.5.0)
max(date) and previous max(date) [message #442819] Wed, 10 February 2010 16:47 Go to next message
dlwixon
Messages: 92
Registered: September 2008
Member
I would like to select the max(date) in a table and then select the next previous max(date) in the same table and then show both dates with the calculated timespan between them.

So it would look like

max(date) next_previous_max(date) timespan_between


I want the max two dates in the table and the time between the two dates.


any suggestions?

[Updated on: Wed, 10 February 2010 16:49]

Report message to a moderator

Re: max(date) and previous max(date) [message #442820 is a reply to message #442819] Wed, 10 February 2010 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
use LEAD or LAG & just subtract the 2 DATEs (results will be in units of DAY)
Re: max(date) and previous max(date) [message #442834 is a reply to message #442820] Wed, 10 February 2010 22:13 Go to previous messageGo to next message
dlwixon
Messages: 92
Registered: September 2008
Member
That will give me the list of dates and previous dates and the number of days between.

However, I need only the max(date) and then only the previous max(date) and not the rest of the dates for each id.

So I need:

ID, max(date), previous max(date), time between

and not

ID, max(date), previous max(date), time between
ID, next max(date), next previous max(date), time between
ID, next max(date), next previous max(date), time between
ID, next max(date), next previous max(date), time between
ID, next max(date), next previous max(date), time between
ID, next max(date), next previous max(date), time between
etc.

[Updated on: Wed, 10 February 2010 22:16]

Report message to a moderator

Re: max(date) and previous max(date) [message #442835 is a reply to message #442834] Wed, 10 February 2010 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I need only the max(date) and then only the previous max(date) and not the rest of the dates for each id.

So limit the number of rows that are returned to ONE row.
Re: max(date) and previous max(date) [message #442840 is a reply to message #442819] Wed, 10 February 2010 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: max(date) and previous max(date) [message #442854 is a reply to message #442819] Thu, 11 February 2010 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select deptno, hiredate, 
  4             row_number() over (partition by deptno order by hiredate desc) rn
  5      from emp
  6    )
  7  select deptno,
  8         max(decode(rn,1,hiredate)) max_date,
  9         max(decode(rn,2,hiredate)) previous_max
 10  from data
 11  group by deptno
 12  order by deptno
 13  /
    DEPTNO MAX_DATE    PREVIOUS_MA
---------- ----------- -----------
        10 17-NOV-1981
        20 23-MAY-1987 19-APR-1987
        30 03-DEC-1981 28-SEP-1981

3 rows selected.

Regards
Michel
Re: max(date) and previous max(date) [message #442944 is a reply to message #442854] Thu, 11 February 2010 09:31 Go to previous messageGo to next message
dlwixon
Messages: 92
Registered: September 2008
Member
Excellent! This is what I wanted. Thanks!
Re: max(date) and previous max(date) [message #501971 is a reply to message #442854] Fri, 01 April 2011 16:14 Go to previous messageGo to next message
inghenryandrade
Messages: 1
Registered: April 2011
Location: Colombia
Junior Member
Shocked ERES UN MOSTRO!!!
Re: max(date) and previous max(date) [message #502002 is a reply to message #501971] Sat, 02 April 2011 11:03 Go to previous message
Solomon Yakobson
Messages: 2459
Registered: January 2010
Location: Connecticut, USA
Senior Member
It all depends on interpretation previous max(date). If we have a set of values 1,2,3,4,4,5,5, I'd say max is 5 and previous max is 4. Michel's solution interprets is max is 5 and previous max is 5:

SQL> create table emp1 as select * from emp;

Table created.

SQL> insert into emp1 select * from emp;

14 rows created.

SQL> with
  2     data as (
  3      select deptno, hiredate,
  4             row_number() over (partition by deptno order by hiredate desc) rn
  5      from emp1
  6    )
  7  select deptno,
  8         max(decode(rn,1,hiredate)) max_date,
  9         max(decode(rn,2,hiredate)) previous_max
 10  from data
 11  group by deptno
 12  order by deptno
 13  /

    DEPTNO MAX_DATE  PREVIOUS_
---------- --------- ---------
        10 23-JAN-82 23-JAN-82
        20 23-MAY-87 23-MAY-87
        30 03-DEC-81 03-DEC-81

SQL> 


But regardless of method it can be done without decode and group by:

SQL> with
  2     data as (
  3      select deptno, hiredate,
  4             lead(hiredate) over (partition by deptno order by hiredate desc) prev_hiredate_method1,
  5             max(hiredate) over (partition by deptno order by hiredate range between unbounded preceding and 1 preceding) prev_hiredate_method2,
  6             row_number() over (partition by deptno order by hiredate desc) rn
  7      from emp1
  8    )
  9  select deptno,
 10         hiredate max_date,
 11         prev_hiredate_method1 max_date_method1,
 12         prev_hiredate_method2 max_date_method2
 13  from data
 14  where rn = 1
 15  order by deptno
 16  /

    DEPTNO MAX_DATE  MAX_DATE_METHOD1 MAX_DATE_METHOD2
---------- --------- ---------------- ----------------
        10 23-JAN-82 23-JAN-82        17-NOV-81
        20 23-MAY-87 23-MAY-87        19-APR-87
        30 03-DEC-81 03-DEC-81        28-SEP-81

SQL>  


SY.
Previous Topic: Last Sequence Number created.
Next Topic: Tracing DML activities
Goto Forum:
  


Current Time: Fri Sep 30 21:03:40 CDT 2016

Total time taken to generate the page: 0.19093 seconds