I have a query that selects a specific row / column from a large database. Assume the return value is '53. ''
I need to get:
1. The row that is 3000 rows above this value.
2. The row that is 3000 rows below this value.
If it turns out that it will be only 2,000 lines above the value, then I need to add the difference to the second query.
Ex.
1. Find 3000th value up (turns out that only 2000 values are present)
2. Find 4000th value down.
Here is how I did it (this is in the stored procedure):
SET @s1 = CONCAT('INSERT INTO List1(STD) SELECT t1.STD FROM ',t1,' AS t1 USE INDEX(STD) WHERE t1.STD < ',inp1,' order by STD DESC limit ', inp2);
PREPARE stmt FROM @s1;
EXECUTE stmt;
SET @lim = inp2+(inp2-(SELECT FOUND_ROWS()));
SET @s2 = CONCAT('INSERT INTO List1(STD) SELECT t1.STD FROM ',t1,' AS t1 USE INDEX(STD) WHERE t1.STD >=', inp1,' order by STD ASC limit ?');
PREPARE stmt FROM @s2;
EXECUTE stmt USING @lim;
SET @minSD1 = (SELECT MIN(STD) FROM List1);
SET @maxSD1 = (SELECT MAX(STD) FROM List1);
It seems horribly around ... there is no better way?
Also, is there no way to use the variable table name in a stored procedure without creating the ANOTHER stored procedure (with the keyword PREPARE)?
source
share