Question 233138 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    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:

    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.

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search