sql - Compare column entry to every other entry in the same column -

i have column of values in sqlite.

value -----  1  2  3  4  5

for each value know how many of other values larger , display result. e.g. value 1 there 4 entries have higher values.

value | count -------------   1   |   4   2   |   3   3   |   2   4   |   1   5   |   0

i have tried nested select statements , using count(*) function not seem able extract correct levels. suggestions appreciated.

many thanks

you can correlated subquery in sqlite:

select value,        (select count(*) t t2 t2.value > t.value) "count" t;

in other databases, use ranking function such rank() or dense_rank(), sqlite doesn't support these functions.

M16