How can I get the next row in rank in "rank of last id"?

Question

Create table

CREATE TABLE goal_implement( id INT, percent INT ); INSERT INTO goal_implement VALUES (1,10), (2,15), (3,20), (4,40), (5,50), (6,20); 

Query

 SELECT id, percent, FIND_IN_SET( percent, ( SELECT GROUP_CONCAT( percent ORDER BY percent DESC ) FROM goal_implement ) ) AS rank FROM goal_implement ORDER BY id DESC 

Result

 id percent rank 6 20 3 5 50 1 4 40 2 3 20 3 2 15 5 1 10 6 

I do not know how to get row(rank) following the last id for example: the last rank id is 3!

Search results

 id percent rank 4 40 2 

Answer 1

To solve the problem, we had 2 language options:

• Php was easy → not fun.

• MYSQL → You have already completed part of the task with Mysql, so I finished it this way.

SQLFIDDLE: Demo

Steps:

• Get last id id + Get row from all percent

   SELECT FIND_IN_SET( percent, (SELECT GROUP_CONCAT( percent ORDER BY percent DESC ) FROM goal_implement )) - 1 into @next_rank FROM goal_implement ORDER BY id DESC LIMIT 1; SELECT GROUP_CONCAT( percent ORDER BY percent DESC ) FROM goal_implement into @str_rank; 

This code will give you the following:

 @next_rank @str_rank 2 50,40,20,20,15,10 

Let the fun begin (launch pbm - Kappa): in MYSQL there is no explode () function.

• Get percent related to @next_rank inside @str_rank

Best done with native function:

• SELECT SUBSTRING_INDEX(@str_rank, ',', @next_rank);

• Result: 50,40

But we are '40' only

-> Find / create a function to extract a line between position +1 and -1 (Big up to Arman P.)

 CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos), LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, ''); 

. SELECT SPLIT_STRING(@str_rank, ',', @next_rank) into @next_percentage;

This will save '40' in @next_percentage

RESULT: (finally)

 SELECT *, @next_rank as rank FROM goal_implement WHERE percent = @next_percentage; 

OUTPUT:

 id percent rank 4 40 2 

PHP version:

$Array_test assumes the array returned by your request

 <?php $array_test = array(array(6,20,3), array(5,50,1), array(4,40,2), array(3,20,3), array(2,15,5), array(1,10,6)); $next_rank = $array_test[0][2] - 1; foreach($array_test as $row) if($row[2] == $next_rank) { print "<pre>"; print_r($row); print "</pre>"; } ?> 

Output:

 Array ( [0] => 4 [1] => 40 [2] => 2 ) 

Source: fooobar.com/questions/198926 / ...

Answer 2

first, get another id of the last rank ... then add it for it. An example is below.

 SELECT id FROM YOUR_TABLE WHERE rank = $last_rank AND id != $last_id ORDER BY id DESC; SELECT * FROM YOUR_TABLE WHERE id = $row['id'] + 1; 

Answer 3

Try the following:

 select * from goal_implement where rank=(select (rank-1) from goal_implement where id=(select max(id) from goal_implement)); 

I hope you will need a way out.