Get Previous and Next record from database and loop them

Question

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?

Are you trying to create a function, a loop or a select? Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 20 '19 at 16:15
Would be simple if you show sample data and expected output. — Juan Carlos Oropeza, May 20 '19 at 16:17
Lets say I have only id field in the table. Would that help? — Ali Zia, May 20 '19 at 16:18
something like this. Sample data and expected output https://stackoverflow.com/a/46409126/3470178 or this https://stackoverflow.com/questions/54890579/how-to-insert-into-one-column-from-two-tables-two-different-column/54907329#54907329 — Juan Carlos Oropeza, May 20 '19 at 16:20
@Strawberry My guess is a typo. for 4 --> `before` should be 3 and `after` should be 5 — Juan Carlos Oropeza, May 20 '19 at 16:29

Juan Carlos Oropeza · Accepted Answer · 2019-05-20T16:33:25.593

1

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.

edited May 20 '19 at 16:33

answered May 20 '19 at 16:28

Juan Carlos Oropeza

47,252
12
78
118

The OP wants 3,4 for 4. – Strawberry May 20 '19 at 16:29
Sorry it was 3,5 for 4 – Ali Zia May 20 '19 at 17:09
1

I find this solution too complicated. It also makes the assumption that the ids have no gaps. – Gordon Linoff May 20 '19 at 17:19
@GordonLinoff In my defense I already said assume there are not gaps and require a sequence if there are gaps. – Juan Carlos Oropeza May 20 '19 at 17:33
If there are no gaps, you need no joins. – Paul Spiegel May 20 '19 at 19:18
@PaulSpiegel How you find previous and next and loop without join? – Juan Carlos Oropeza May 20 '19 at 19:20
You don't need to **find** anything. If there are no gaps, Then you just need `ID - 1` and `ID + 1`. There are only two exceptions, which can be handled with CASE and subquery. – Paul Spiegel May 20 '19 at 19:25
@PaulSpiegel You are right. But I assume OP also want some other columns beside the ID. The sample queries include `news.*` otherwise as you said would be a trivial query. – Juan Carlos Oropeza May 20 '19 at 19:28

score 1 · Answer 2 · answered May 20 '19 at 20:14

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.

Gordon Linoff · Answer 3 · 2019-05-20T17:45:55.270

0

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.

edited May 20 '19 at 17:45

answered May 20 '19 at 17:17

Gordon Linoff

1,242,037
58
646
786

How the second query works to find prev and next? Or is just for row with id 1? – Juan Carlos Oropeza May 20 '19 at 17:35
@JuanCarlosOropeza . . . If the `id` is the minimum, then the first `max()` returns `NULL` and the second is returned. Otherwise, the "previous" id will be returned from the first `max()`. – Gordon Linoff May 20 '19 at 17:46
I see now. So only return the prev and max for the row with `id = 1` like a function? In this case `@row_id = 1` – Juan Carlos Oropeza May 20 '19 at 17:48

Get Previous and Next record from database and loop them

3 Answers3