Imagine I have the following SQL table:

| id | price | start_time |
---------------------------
|  1 |  0.1  | 2023-01-01 |
|  2 |  0.3  | 2023-03-01 |
|  3 |  0.2  | 2023-02-01 |

But then I want to query the prices in that table in a way that I can also get the end time as the start time of the next in time column. So, as an example, if I want to query all the entries in the table I would get something like this:

| id | price | start_time | end_time   |
----------------------------------------
|  1 |  0.1  | 2023-01-01 | 2023-02-01 | // end_time = start_time of the next entry
|  3 |  0.2  | 2023-02-01 | 2023-03-01 |
|  2 |  0.3  | 2023-03-01 |            |

But, I would also like to query that table with others filters, as an example, all entries whose prices are lower than 0.25, then I expect:

| id | price | start_time | end_time   |
----------------------------------------
|  1 |  0.1  | 2023-01-01 | 2023-02-01 |
|  3 |  0.2  | 2023-02-01 | 2023-03-01 | end_time = start_time of entry with id 2

So even that the entry with id 2 is filtered out, its start_time is still used as end_time of one of the entries.

Is this possible to achieve with one single query? I am bit lost on how to solve this approach without doing multiple queries.

>Solution :

Assuming you’re working with a database that supports window functions, you could use lead():

select *, lead(start_time) over (order by start_time) as end_time
from mytable

With where clause it can be :

select * from (
  select *, lead(start_time) over (order by start_time) as end_time
  from mytable
) as s
where price < 0.25

check demo Tested on mysql