I have table:
| Owner | Book | Date |
|---|---|---|
| John | Vile Bodies by Evelyn Waugh | 2009-05-20 |
| Tom | Vile Bodies by Evelyn Waugh | 2009-05-22 |
| Jim | Moab is my Washpot by Stephen Fry | 2009-05-26 |
| Kate | Moab is my Washpot by Stephen Fry | 2009-06-02 |
How can I get table:
| Owner | Book | Pick_Date | Gave_Back_Book_Date |
|---|---|---|---|
| John | Vile Bodies by Evelyn Waugh | 2009-05-20 | 2009-05-22 |
| Tom | Vile Bodies by Evelyn Waugh | 2009-05-22 | |
| Jim | Moab is my Washpot by Stephen Fry | 2009-05-26 | 2009-06-02 |
| Kate | Moab is my Washpot by Stephen Fry | 2009-06-02 |
>Solution :
It seems you are looking for the lead() window function which allows you to access columns from the "next" row(s)
select owner, book, "date" as pick_date,
lead(date) over (partition by book order by "date") as return_date
from the_table
order by book, "date"
Talking about the "next" row only makes sense if the rows are sorted, that’s why the order by "date" is needed in the definition of the window