How to get previous and latest date and its details in SQL

Question

I have a table with the following data:

create table tempdata(account varchar2(20)not null,bookid number(10),seqno number(20) not null,book_date date, book1 number(10),
book2 number(10),book3 number(10))

insert into tempdata values('123',101,09,add_months((sysdate),-1),100,120,130);

insert into tempdata values('123',101,10,sysdate),70,60,100)

select * from tempdata;

ACCOUNT BOOKID  SEQNO   BOOK_DATE               BOOK1   BOOK2   BOOK3

123      101     9      9/22/2015 10:05:28 AM   100      120    130

123      101     10     10/22/2015 10:01:42 AM  70       60     100

I need to output something like the following in order to create another temp table with latest book details including the previous date and latest date:

ACCOUNT BOOKID  SEQNO     Previous_DATE          Latest_date           BOOK1    BOOK2   BOOK3

123      101     10    9/22/2015 10:05:28 AM  10/22/2015 10:01:42 AM          70     60      100

Answer 1

Here I am assuming that you want data for a unique account and bookid combination.

SELECT T1.ACCOUNT, T1.BOOKID, T1.SEQNO,T1.PREVIOUS_DATE, 
T1.BOOK_DATE AS  LATEST_DATE , T1.BOOK1, T1.BOOK2, T1.BOOK3 
FROM (
    SELECT T.* ,ROW_NUMBER() OVER (PARTITION BY ACCOUNT,bookid ORDER BY BOOK_DATE desc) as rno, 
    LAG(TO_CHAR(BOOK_DATE), 1, 0) OVER (ORDER BY BOOK_DATE) AS PREVIOUS_DATE 
    FROM TEMPDATA T) T1
WHERE T1.RNO =1

Answer 2

The LAG and ROW_NUMBER analytic functions would come in handy here:

select account,
       bookid,
       seqno,
       previous_date,
       latest_date,
       book1,
       book2,
       book3
from   (select account,
               bookid,
               seqno,
               lag(book_date) over (partition by account order by book_date) previous_date,
               book_date latest_date,
               book1,
               book2,
               book3,
               row_number() over (partition by account order by book_date) rn
        from   tempdata)
where  rn = 1;

ACCOUNT                  BOOKID      SEQNO PREVIOUS_DATE         LATEST_DATE                BOOK1      BOOK2      BOOK3
-------------------- ---------- ---------- --------------------- --------------------- ---------- ---------- ----------
123                         101          9                       22/09/2015 14:34:06          100        120        130

N.B. I've made the assumption that you want this information for each account. If the grouping needs to be changed (eg. maybe it's account and bookid, or just bookid) then you'll need to amend the partition by clauses appropriately.