oracle - How to get previous and latest date and its details in SQL -
i have table 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 tempdata values('123',101,09,add_months((sysdate),-1),100,120,130); insert tempdata values('123',101,10,sysdate),70,60,100) select * tempdata; account bookid seqno book_date book1 book2 book3 123 101 9 9/22/2015 10:05:28 100 120 130 123 101 10 10/22/2015 10:01:42 70 60 100
i need output following in order create temp table latest book details including previous date , latest date:
account bookid seqno previous_date latest_date book1 book2 book3 123 101 10 9/22/2015 10:05:28 10/22/2015 10:01:42 70 60 100
here assuming want data unique account
, bookid
combination.
select t1.account, t1.bookid, t1.seqno,t1.previous_date, t1.book_date latest_date , t1.book1, t1.book2, t1.book3 ( select t.* ,row_number() on (partition account,bookid order book_date desc) rno, lag(to_char(book_date), 1, 0) on (order book_date) previous_date tempdata t) t1 t1.rno =1
Comments
Post a Comment