CODEWITHSUNDEEP
mysqlsql
Ali Zia
Ali Zia

Reputation: 3875

Get Previous and Next record from database and loop them

I have a table with IDs from 1 to 8. I want something like this

If i'm on 4, I should get 3,5

If i'm in on 1, I should get 8,2

If in on 8, I should get 7, 1

Basically looping through the table records

This is my current code

-- previous or last, if there is no previous
SELECT *
FROM news
WHERE id < 1 OR id = MAX(id)
ORDER BY id DESC
LIMIT 1

-- next or first, if there is no next
SELECT *
FROM news
WHERE id > 1 OR id = MIN(id)
ORDER BY id ASC
LIMIT 1

But it says Invalid use of group function. Any help?

Upvotes: 0

Views: 638

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31772

I guess that you want to show "prev" and "next" buttons, when the user views a news article. I would get the previous and the next ID in the main query, when you fetch the article data:

select n.*, -- select columns you need
  coalesce(
    (select max(n1.id) from news n1 where n1.id < n.id ),
    (select max(id) from news)
  ) as prev_id,
  coalesce(
    (select min(n1.id) from news n1 where n1.id > n.id ),
    (select min(id) from news)
  ) as next_id
from news n
where n.id = ?

db-fiddle demo

Now you can use prev_id and next_id for your buttons, or prefetch the corresponding articles with a simple select * from news where id = ? query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can remove the filtering in your approach and add logic to the ORDER BY:

(SELECT n.*
 FROM news
 ORDER BY (id < 1), id DESC
 LIMIT 1
) UNION ALL
(SELECT n.*
 FROM news
 ORDER BY (id > 1), id ASC
 LIMIT 1
) ;

If you want the id values in one row, you can use aggregation:

select coalesce(max(case when id < 1 then id end), max(id)) as prev_id,
       coalesce(min(case when id > 1 then id end), min(id)) as next_id
from news n;

In both cases, 1 is a sample id and the "1" can be replaced with any value.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

If id is sequential you can do this:

SQL DEMO

SELECT o.id, 
       COALESCE(b.id, (SELECT MAX(ID) FROM Table1)) as before_id,
       COALESCE(a.id, (SELECT MIN(ID) FROM Table1)) as after_id
FROM Table1 o
LEFT JOIN Table1 b
  ON o.id = b.id + 1
LEFT JOIN Table1 a  
  ON o.id = a.id - 1
ORDER BY o.id

OUTPUT

| id | before_id | after_id |
|----|-----------|----------|
|  1 |         8 |        2 |
|  2 |         1 |        3 |
|  3 |         2 |        4 |
|  4 |         3 |        5 |
|  5 |         4 |        6 |
|  6 |         5 |        7 |
|  7 |         6 |        8 |
|  8 |         7 |        1 |

If ids are not sequential you need use row_number() (mysql ver 8+) or session variables to create a sequence.

Upvotes: 1

Related Questions