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.
Comments
Post a Comment