Create column corresponding to entry rank position in SQL

Question

I am using SQL and I have a table that looks like:

variable1   123
variable2   39
variable3   993
variable4   2

and I would like to add a column that corresponds to the rank of the integer, had that column been sorted. ie:

variable1   123    3
variable2   39     2
variable3   993    4
variable4   2      1

So far I haven't been able to get the syntax correct - I can sort or order by that column but not generate the correct ranking variable.

Many thanks.

Answer 1

The best performant way is to use variables:

select t.*,
       (@rn := @rn + 1) as rank
from table t cross join
     (select @rn := 0) params
order by intcol;

Note: this technically does a row_number() but the two are equivalent for your sample data, because there are no ties.

Answer 2

it sounds like you'd need to create a trigger before insert that would update that column to increase the rank integer for each variable that held a value higher than the value which you are about to insert by 1.