CODEWITHSUNDEEP
mysqlsqlquery-optimization
python
python

Reputation: 4521

How to get the next row value only in MySQL

In my table I have two columns (id, score):

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------

If you use order DESC by clause on score then the overall table will be:

+----+-------+
| Id | Score |
+----+-------+
| 3  | 4.00  |
| 5  | 4.00  |
| 4  | 3.85  |
| 2  | 3.65  |
| 6  | 3.65  |
| 1  | 3.5   |
+----+-------+

My question is how would get the next score value when you are iterating over it.

For example, if I am in row 1 then I want to access the row 2 score value which is 4.00. I have tried using inner join but it is not working. I only want to access the next row score value. Inner join limit 1 is giving me upper row values as well. If you see my query below:

select 
    c2.score
from
    rank c1
        join
    rank c2 ON c2.id != c1.id
order by c1.score DESC , c2.score DESC
limit 1

My end result should like this : I need do rank according to score

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Upvotes: 0

Views: 669

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I have no idea what your discussion about getting the next score has to do with calculating the rank. If you want to calculate the rank without variables, then you can do:

select r.*,
       (select count(distinct r2.score)
        from rank r2
        where r2.score >= r.score
       ) as rank
from rank r;

In MySQL, this normally more efficient using variables:

select r.*,
       (@rn := if(@s = r.score, @rn,
                  if(@s := r.score, @rn + 1, @rn + 1)
                 )
       ) as rank
from rank r cross join
     (select @rn := 0, @s := -1) params
order by r.score desc;

Upvotes: 1

Related Questions