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

Popular posts from this blog

Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.12:test (default-test) on project.Error occurred in starting fork -

windows - Debug iNetMgr.exe unhandle exception System.Management.Automation.CmdletInvocationException -

configurationsection - activeMq-5.13.3 setup configurations for wildfly 10.0.0 -