C. Reed C. Reed - 10 months ago 83
SQL Question

Retrieve Rank from sqlite table

Say I have a table in an sqlite DB with two fields: name and age.


How can I query the sqlite table for Zek and determine that he is the oldest? More generally, say I have millions of names and ages and I want to query a specific entry, say name="Juju bear", and find the rank of the entry by a different field, e.g. that "Juju bear" is ranked 133455 (by age).




You can use a subquery to count the number of people with a higher age, like:

select  p1.*
,       (
        select  count(*) 
        from    People as p2
        where   p2.age > p1.age
        ) as AgeRank
from    People as p1
where   p1.Name = 'Juju bear'