CODEWITHSUNDEEP
mysqlsqlsql-order-by
Pragnesh Chauhan
Pragnesh Chauhan

Reputation: 8476

increment if not same value of column

i want to increment order if rank value is not same

i have tried following query

SQL FIDDLE

select rank, @rownum := @rownum + 1 AS `myorder`
from myRankorder, (SELECT @rownum := 0) AS a
order by rank desc

but it return this result

RANK   MYORDER
10     1
8      2
6      3
6      4
4      5
4      6
4      7
3      8
2      9
1      10

Desired Output

RANK   MYORDER
10     1
8      2
6      3
6      3
4      4    <-- if rank value is same do not increment myorder value
4      4
4      4
3      5
2      6
1      7

Upvotes: 1

Views: 197

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

It is a bit more complicated. You need an additional variable:

select rank,
       if (@prevrank = rank, @rownum, @rownum := @rownum + 1) AS `myorder`,
       @prevrank := rank
from myRankorder cross join
     (SELECT @rownum := 0, @prevrank := -1) const
order by rank desc;

Just one point. MySQL does not guarantee the order of evaluation of expressions in the select. In practice this works, but it is not guaranteed.

Upvotes: 2

Related Questions