Sql for generating previous and next links in MySql

Question

This has me completely stumped. From a set of numbers - for this example 1 to 20, I'm trying to do previous and next links, so they end up like this: I'll be supplying the number to start from.

1       6           11          16
2       7           12          17
3       8           13          18
4       9           14          19
5       10          15          20
n-5    p-6 n-10   p-11 n-15    p-16

Have you any idea how to do the sql for something like this?

I was trying to build on this one, but no luck:

SELECT c.id,
  (SELECT MAX(p.id) FROM mytable p WHERE p.id < c.id AND p.country = 'us') prev_id,
  (SELECT MIN(n.id) FROM mytable n WHERE n.id > c.id AND n.country = 'us') next_id
FROM mytable as c WHERE c.id = 5;

Table data

    "id"    "country"
"1"     "US"
"2"     "US"
"3"     "US"
"4"     "US"
"5"     "US"
"6"     "US"
"7"     "US"
"8"     "US"
"9"     "US"
"10"    "US"
"11"    "US"
"12"    "US"

Desired output

number provided by me = 10

prev  | next
-------------
6     | 10

Not 4 columns. One one column. If I gave you No. 10, the previous would be 6 and next would be 10. See p-6 n-10 in my example. — jmenezes
– jmenezes, Commented Sep 22, 2013 at 18:35
Can you please provide the desired output based on your sample data (you already posted)? — PM 77-1
– PM 77-1, Commented Sep 22, 2013 at 18:44

PM 77-1 · Accepted Answer · 2013-09-22 20:09:30Z

1

Here is MySQL solution:

SET @number=15;
SET @counter=0;


SELECT x.id, @number as next
FROM (
SELECT id, (@counter:=@counter+1) as line FROM TableData WHERE id <=@number ORDER BY id 
) x
WHERE x.line = @counter-5;

number is what you specify.

You can see how it works in SQL Fiddle. Feel free to change its data and test,

edited Sep 22, 2013 at 20:09

answered Sep 22, 2013 at 20:01

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jmenezes Over a year ago

Right. Thanks a lot for this. I tested here and it seems to be working nicely.

PM 77-1 Over a year ago

Link now works. I added 'CA' (not 'US') record and removed some lines so IDs are not sequential.

jmenezes Over a year ago

Your solution works fantastic. Thanks very much for helping on this. BTW, is that really you in your avatar image?

PM 77-1 Over a year ago

No, it's not me. Got this image from internet.

Collectives™ on Stack Overflow

Sql for generating previous and next links in MySql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related