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

Select next and previous id of table with same category – Mysql

I have table:

id topic subjectId content
1 Topic1 5 con…
2 topic2 6 con…
3 topic3 6 con….
4 Topic4 5 con…
5 topic5 6 con…
6 topic6 5 con….

currently I selected id 4 which have subjectId 5, How to select previous id with subjectId 5, and next id with subjectId

Tags:

3

Answers


  1. 0

    I’m not 100% sure I understand your question, but from what I think your trying to do is get id 1 to return based on pass id 4 to a query.

    This query does that, if you make it an stored procedure and pass an id, it will return the data you need to do whatever that is:

    SELECT TOP(1) * FROM #test_table
WHERE (id = @CurrentId
OR subjectid = (SELECT subjectid FROM #test_table WHERE id = @CurrentId))
AND id < @CurrentId
ORDER BY id DESC
    Login or Signup to reply.
  2. 0

    You could retrieve the next and previous ids at the same time as fetching the current topic:

    SELECT *,
    (SELECT id FROM topics WHERE subjectId = t.subjectId AND id < t.id ORDER BY id DESC LIMIT 1) AS prev,
    (SELECT id FROM topics WHERE subjectId = t.subjectId AND id > t.id ORDER BY id ASC LIMIT 1) AS next
FROM topics t
WHERE t.id = 4;

    Which will return:

    id topic subjectId content prev next
    4 Topic4 5 con… 1 6

    Here’s a db<>fiddle

    Login or Signup to reply.
  3. 0

    Since MySQL 8.0 you can use window function lead & lag in next way:

    select 
    id, subject_id, 
    lag(subject_id) over w as prev_subject_id, 
    lead(subject_id) over w next_subject_id
from test
window w AS (ORDER BY id asc);

    https://sqlize.online/s/gi

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

Back To Top
Search