find a minimum number of rows whose value is less than the value of the present row

if your MySQL version support ROW_NUMBER window function

You can try to use ROW_NUMBER window function with subtraction one

Query #1

SELECT *,ROW_NUMBER() OVER(ORDER BY value) - 1 count_less_than
FROM T
ORDER BY ID;
id value count_less_than
1 2 3
2 4 4
3 1 2
4 0 0
5 0.2 1

View on DB Fiddle

if your MySQL version didn’t support ROW_NUMBER window function

you can try to use subquery with count to calculate number

Query #1

SELECT *,
       (SELECT COUNT(*)
       FROM T tt
       WHERE tt.value < t.value) count_less_than
FROM T t
ORDER BY ID;
id value count_less_than
1 2 3
2 4 4
3 1 2
4 0 0
5 0.2 1

View on DB Fiddle

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top