How to get 1 row before and 1 row after what query specified?

Question

Imaging a query that delivers "posts" in a specific time period between "from" and "to". A simple query.

Additionally, I need to get ONE record BEFORE and ONE record AFTER what the original query specified.

For example, a table holds these posts:

1 hello  20.08.2023
2 hi     21.08.2023
3 please 22.08.2023
4 bye    23.08.2023

For get_posts("from" => '21.08.2023', to => '22.08.2023') the origin query delivers:

2 hi
3 please

Now I need to add one row before (1 hello) and one row after (4 bye). Possibly as subquery inside the original query?

I read about window functions or subqueries, but I do not know how to start.

A [mcve] would make this much clearer. Also remove non-related tags. — jarlh, Aug 18 '23 at 18:43
Do you work with Postgres or MySQL. Please specify *one*. And your version number. The best solution may depend on it. Also, the exact table definition matters. Provide a `CREATE TABLE` statement showing data types and constraints. — Erwin Brandstetter, Aug 18 '23 at 21:28

score 2 · Accepted Answer · edited Aug 18 '23 at 23:48

Assuming current Postgres.

( -- one lead
SELECT *
FROM   posts
WHERE  time_stamp < '2020-08-21'
ORDER  BY time_stamp DESC
LIMIT  1
)  -- parentheses required

UNION ALL  -- core rows
SELECT *
FROM   posts
WHERE  time_stamp >= '2020-08-21'
AND    time_stamp <  '2020-08-24'  -- !

UNION ALL
( -- one lag
SELECT *
FROM   posts
WHERE  time_stamp >= '2020-08-24'
ORDER  BY time_stamp
LIMIT  1
)  -- parentheses required

Typically, you don't need an outer ORDER BY with this. But consider:

Are results from UNION ALL clauses always appended in order?

Unless the table is trivially small, you need an index on posts(time_stamp) to make this fast.

Beware of BETWEEN when working with timestamp types. What you show looks like date, but timestamptz would make a lot more sense for the use case.

Always use ISO format with date and time literals, which is unambiguous with any locale and datestyle setting. The meaning of '20.08.2023' depends on settings of the current session and breaks easily.

Muhammad Hamid Rafique · Answer 2 · 2023-08-18T18:47:58.937

1

SELECT post_id, post_date
FROM (

--All posts between the timeframe
SELECT post_id, post_date
FROM posts
WHERE post_date BETWEEN '2020-03-04 19:19:34' AND '2020-03-06 19:19:34'

UNION
--One before the from date
SELECT post_id, post_date
FROM posts
WHERE post_date = (
    SELECT MAX(post_date)
    FROM posts
    WHERE post_date < '2020-03-04 19:19:34'
)

UNION
--one after the to date
SELECT post_id, post_date
FROM posts
WHERE post_date = (
    SELECT MIN(post_date)
    FROM posts
    WHERE post_date > '2020-03-06 19:19:34'
)
) AS combined_posts
ORDER BY post_date;

You can also make the query shorter by using lag and lead functions instead of subqueries.

edited Aug 18 '23 at 18:47

answered Aug 18 '23 at 18:43

Muhammad Hamid Rafique

71
6

You should be using `UNION ALL`, instead of `UNION`, to avoid the unnecessary de-dupe. Also, it is not necessary to turn the UNION into a derived table. It won't make any difference to performance in this case, as the temporary table is needed for the sort anyway. [db<>fiddle](https://dbfiddle.uk/dkEd2NbX) – user1191247 Aug 18 '23 at 20:49
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 21 '23 at 12:09

How to get 1 row before and 1 row after what query specified?

2 Answers2