jerrymouse jerrymouse - 2 months ago 7
MySQL Question

Get number of rows with values less than current row's value

My mysql table is:

Id | value | count_of_past_lower_values
1 | 120 | 0
2 | 210 | 1
3 |150 | 1
4 |140 | 1
5 |200 | 3


Given id and values, I have to update 3rd col. I made a query using
SUM(IF(value<X,1,0))
but not able to identify right expression for X.

Answer

Issuing a self-left-join with conditional sum would suffice:

select 
  a.id, 
  a.value, 
  sum(case when a.value > b.value then 1 else 0 end) as count_of_past_lower_values
from yourtable a
left join yourtable b on a.id > b.id
group by a.id, a.value
order by a.id

Result

 id | value | count_of_past_lower_values
----+-------+----------------------------
  1 |   120 |                          0
  2 |   210 |                          1
  3 |   150 |                          1
  4 |   140 |                          1
  5 |   200 |                          3
Comments