How to get previous and last date and its data 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 temporary table with the latest information about the book, including the previous date and last 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 assume that you need data for a unique combination of account and bookid .

 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 analytic functions LAG and ROW_NUMBER would be useful 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 

NB I made the assumption that you want this information for each account. If you need to change a group (for example, perhaps its account and book or just a book), you need to change the section of the articles accordingly.

Answer 3

 SELECT ACCOUNT, BOOKID, MAX(SEQNO), (SELECT * FROM (SELECT BOOK_DATE, row_number() over (ORDER BY BOOK_DATE desc) AS row_num) t WHERE row_num = 2) AS Previous_DATE, MAX(BOOK_DATE) AS Latest_date, (SELECT a.BOOK1 FROM tempdataa a WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) FROM tempdata WHERE ACCOUNT = a.ACCOUNT AND BOOKID = a.BOOKID)) AS BOOK1, (SELECT a.BOOK2 FROM tempdataa a WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) FROM tempdata WHERE ACCOUNT = a.ACCOUNT AND BOOKID = a.BOOKID)) AS BOOK2, (SELECT a.BOOK3 FROM tempdataa a WHERE a.BOOK_DATE = (SELECT MAX(BOOK_DATE) FROM tempdata WHERE ACCOUNT = a.ACCOUNT AND BOOKID = a.BOOKID)) AS BOOK3 FROM tempdata GROUP BY ACCOUNT, BOOKID