How can I fetch the row of next in rank to the "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 don't know how to fetch the row(rank) that is next on the last id for example: last id's rank is 3!

Want result

id  percent rank
4   40      2

Answer 1

To resolve your question we had 2 options of languages :

• Php was easy --> not fun.

• MYSQL --> Your already did a part of the job with Mysql so i finished it this way.

SQLFIDDLE : Demo

Steps :

• Get the last id's rank + Get string of all percentage

  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 get you this :

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

Let's the fun begin (pbm starting - Kappa) : there is not any explode() function in MYSQL.

• Get percentage related to @next_rank inside @str_rank

Best we can do with native function is :

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

• Result : 50,40

But we '40' only

--> Let's find / create a function to extract string between +1 and -1 position (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 store '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 is supposed the array returned by your query

<?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 : Stackoverflow : Equivalent of explode

Answer 2

first, get the other id of the last rank... then add one for it. Example 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 this:

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

O hope you will desired output.