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 |
CodePudding user response：
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
CodePudding user response：
You have to alter the table with
ALTER TABLE table_name
ADD column_name datatype;
for adding a column, or
ALTER TABLE table_name
MODIFY column_name datatype;
for modify it. Then you have to alter every line of the table that is already into it.