1

I have a table with an ID field, unique, autoincremental.

I need to, given certain ID, retrieve next 3 records even when given ID is the last one.

Table:

ID       
1         
2         
3         
4         
5         
6         
7     

What I need:

Given ID is 1 => retrieve 2,3,4  
Given ID is 2 => retrieve 3,4,5 

.....

Given ID is 5 => retrieve 6,7,1  
Given ID is 6 => retrieve 7,1,2  
Given ID is 7 => retrieve 1,2,3  

How can I do this?

4

Let me try for a pure SQL solution:

SELECT id
FROM  
   (  ( SELECT *, 0 AS chck
        FROM tableX
        WHERE id > @givenID
        ORDER BY id
        LIMIT 3
      )
    UNION ALL
      ( SELECT *, 1 
        FROM tableX
        WHERE id <= @givenID      -- just in case of a small table (< 3 rows) 
        ORDER BY id
        LIMIT 3
      )
   ) AS t
ORDER BY chck, id
LIMIT 3 ;

Test at SQLfiddle.


The query is basically an improvement on Jehad's answer. It doesn't use a variable (@rank) and the two internal LIMITs will result in improved efficiency on a large table.

Assuming there is an index on id, the two subqueries will use the index and only need to fetch 3 ids and 3 rows each from the table. The external table (t) after the UNION will have a maximum of 6 rows to sort.

| improve this answer | |
2

If you prefer not using stored procedures, here is my way:

set @my_id=7; 
set @rank=0; 
select id from 
    (  
        (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) 
        union 
        (select id, @rank:=@rank+1 as x from t where id < @my_id order by id)
    ) my_table 
limit 3;

You change the value of @my_id accordingly.

Examples:
select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
7 rows in set (0.00 sec)

set @my_id=7; 
set @rank=0; 
select id from 
    ->     (  
    ->         (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) 
    ->         union 
    ->         (select id, @rank:=@rank+1 as x from t where id < @my_id order by id)
    ->     ) my_table 
    -> limit 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

set @my_id=5; 
set @rank=0; 

select id from 
    ->     (  
    ->         (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) 
    ->         union 
    ->         (select id, @rank:=@rank+1 as x from t where id < @my_id order by id)
    ->     ) my_table 
    -> limit 3;
+------+
| id   |
+------+
|    6 |
|    7 |
|    1 |
+------+
3 rows in set (0.00 sec)

set @my_id=1; 
set @rank=0; 
select id from 
    ->     (  
    ->         (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) 
    ->         union 
    ->         (select id, @rank:=@rank+1 as x from t where id < @my_id order by id)
    ->     ) my_table 
    -> limit 3;
+------+
| id   |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)
| improve this answer | |
1

Something like this should get you started. The procedure will get the correct rows. It would have been a lot easier if you hadn't wanted to restart at the beginning when you reached the end. :)

 delimiter $$
 create procedure getRows(in inID int)
 begin
      declare maxID int;
      declare IDPlusOne int;
      declare IDPlusTwo int;
      declare IDPlusThree int;

      declare strIDs varchar(100);
      declare strSQL varchar(8000);

      set maxID = (select max(id) from testTable);
      set inID = (case when inID > maxID then inID = maxID else inID end);
      set IDPlusOne = (case when maxID < inID + 1 then inID + 1 - maxID else inID + 1 end);
      set IDPlusTwo = (case when maxID < inID + 2 then inID + 2 - maxID else inID + 2 end);
      set IDPlusThree = (case when maxID < inID + 3 then inID + 3 - maxID else inID + 3 end);

      set strIDs = (select concat(IDPlusOne,',',IDPlusTwo,',',IDPlusThree));
      set strSQL = (select concat('select * from testTable where ID in (',strIDs,')'));

      set @s = strSQL;
      prepare stmt from @s;
      execute stmt;

 end$$
| improve this answer | |
0

I've made a Stored Procedure where you can send the STARD_ID and the QUANTITY of rows.

Example:

mysql> SELECT * FROM test.tbl;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

mysql> CALL test.rows(6,4);;
+---------+----------+----------+
| STR     | START_ID | QUANTITY |
+---------+----------+----------+
| 6,7,1,2 |        6 |        4 |
+---------+----------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `rows`(
IN eID INT(6),
IN eQTY INT(6))
BEGIN

DECLARE vQTY INT(6) DEFAULT 0;
DECLARE vCOUNTER INT(6) DEFAULT 0;

SET vCOUNTER    =0;
SET vQTY        =eQTY;
SET @START_ID   =eID;
SET @OUTPUT_STR ="";

WHILE vCOUNTER<vQTY DO
    # [ IF THE COUNTER = 0, SET THE @START_ID LIKE @NEXT_ID ]
    IF (vCOUNTER=0) THEN
        SET @NEXT_ID=@START_ID;
    ELSE
        SET @NEXT_ID=(SELECT ID FROM test.tbl WHERE ID>@NEXT_ID ORDER BY ID ASC LIMIT 0,1);
    END IF;
    # [ IF THERE'S NOT ANY ID GREATER THAN THE LAST, RESTART ]
    IF (@NEXT_ID IS NULL) THEN
        SET @NEXT_ID=(SELECT ID FROM test.tbl ORDER BY ID ASC LIMIT 0,1);
    END IF;
    # [ INCREMENTING COUNTER ] 
    SET vCOUNTER=vCOUNTER+1;
    # [ OUTPUT STRING ]
    SET @OUTPUT_STR=CONCAT(@OUTPUT_STR,@NEXT_ID,IF(vCOUNTER=vQTY,"",","));
END WHILE;
# [ OUTPUT QUERY ]
SELECT @OUTPUT_STR AS STR,eID AS START_ID,vQTY AS QUANTITY;
END
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.