Make a select with max and min passing condition to each of the two

Question

When a post is accessed, I need, in addition to returning the information of this posts, to return the previous one if it exists and the next one.

I would like to know if there is a way to select MAX(id) and MIN(id) in a single query/select, passing a condition for each one of them. Example of what I’m trying to do in Laravel and I’ll write it in SQL to make it easier too

Laravel:

$query = Post::query();
$query = $query->from('posts')->select(DB::raw('MAX(id), MIN(id)'))->whereRaw("id < {$id} and id > {$id}")->first();

SQL:

select MAX(id), MIN(id) from `posts` where id < 5 and id > 5 limit 1

The id variable is the post id value. In this example, it has the value 5. The query I’m doing is to get the MAX and MIN referring to this id, but I also need to get the info of the post that the user accessed.

The DB has post id number 4 and number 6. That is, I need to get the information from posts number 4, 5 and 6 in this example.

The where condition will never be true, but I cannot use or. The first condition is for MAX and the second for MIN. If I use the or, the biggest id will come of the DB.

I need to get the min and max value compared to a value. That is, as I explained above. If the id is 5, I need to get the largest existing id() below that value and I need to get the smallest value above it. In my case, from the information I have in the DB, it would be id 4, 5 and 6

Is it possible in a single consultation or do I really have to do more than one?

Yes, but I cannot use or. The first condition is for MAX and the second for MIN. If I use the or. The biggest id will come of the DB — 15 hours ago
A minimal reproducible example would make this much clearer. — 15 hours ago
I don't know how to be clearer. I need to get the min and max value compared to a value. That is, as I explained above. If the id is 5, I need to get the largest existing id() below that value and I need to get the smallest value above it. In my case, from the information I have in the DB, it would be id 4, 5 and 6 — 15 hours ago
Sample table data and the expected result – all as formatted text (no images, no links). — 15 hours ago

score 6 · Accepted Answer · 2023-02-15 15:10:10Z

6

Yes, you can do it with case-when

select MAX(
           CASE
               WHEN id < 5 THEN id
               ELSE NULL
           END
       ), MIN(
           CASE
               WHEN id > 5 THEN id
               ELSE NULL
           END
       )
from `posts` 
where id <> 5

EDIT

Laravel equivalent, as shared by Gabriel Edu in the comment-section:

$query = Post::query();
$query = $query->from('posts')->
         select(DB::raw("MAX(CASE WHEN id < {$id} THEN id ELSE null END), MIN(CASE WHEN id > {$id} THEN id ELSE null END)"))->first();

edited 13 hours ago

answered 15 hours ago

Lajos Arpad

60.1k35 gold badges96 silver badges170 bronze badges

4

1

Very good, but I didn't know that. It helped a lot. Since this is for Laravel, I'll add it here as I did to help someone else. CODE: $query = Post::query(); $query = $query->from('posts')->select(DB::raw("MAX(CASE WHEN id < {$id} THEN id ELSE null END), MIN(CASE WHEN id > {$id} THEN id ELSE null END)"))->first();

– Gabriel Edu

15 hours ago
1

@GabrielEdu thanks for the Laravel code chunk, added it into my answer. If this answer solved your problem, then you might consider accepting it as the correct answer.

– Lajos Arpad

15 hours ago
I think that the LIMIT 1 clause could be removed in the query.

– UltraCommit

13 hours ago
1

@UltraCommit yes, your observation is correct, removed it.

– Lajos Arpad

13 hours ago

Add a comment
|

score 3 · Accepted Answer · 2023-02-15 13:21:32Z

The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition.

Try this:

  SELECT ID,
         LAG (id) OVER (ORDER BY NULL) ONE_SHIFT_FORWARD,
         LEAD (id) OVER (ORDER BY NULL) ONE_SHIFT_BACKWARD
    FROM POSTS
ORDER BY ID ASC;

SELECT *
  FROM (  SELECT ID,
                 LAG (id) OVER (ORDER BY NULL) ONE_SHIFT_FORWARD,
                 LEAD (id) OVER (ORDER BY NULL) ONE_SHIFT_BACKWARD
            FROM POSTS
        ORDER BY ID ASC)
 WHERE id = 5;

Very good, in SQL it works, but in Laravel I couldn't reproduce this. I researched, tested, but this way it gives 'ASC or DESC' error. But thanks a lot for the alternative. — 15 hours ago
Try to remove the ASC word. Write only: ORDER BY ID (without the ASC word, which means ASCENDING). — 13 hours ago

score 2 · Accepted Answer · 2023-02-15 13:24:19Z

You may use lead and lag to access the values before and after the current row.
You may then use those to select the post with a given id and the values before and after in a single select.

The following query

select * 
    from (
    select 
        p.*,
        lead(id) over(order by id) _lead,
        lag(id) over(order by id) _lag
    from post p
) x
where 23 in (id, _lead, _lag);

results in

id	text	_lead	_lag
15	fifteen	23	10
23	twentythree	24	15
24	twentyfour	50	23

With the following setup:

Schema (MySQL v8.0)

create table post (
    id integer,
    text varchar(50)
);
    
insert into post(id, text)
values
( 10, 'ten'),
( 15, 'fifteen'),
( 23, 'twentythree'),
( 24, 'twentyfour'),
( 50, 'fifty');

View on DB Fiddle

Very good, in SQL it works, but in Laravel I couldn't reproduce this. I researched, tested, but this way it gives 'ASC or DESC' error. But thanks a lot for the alternative. — 15 hours ago

Make a select with max and min passing condition to each of the two