sql - Retrieve Rank from sqlite table

Question

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

Bob|40
Rob|50
Zek|60

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).

Thanks,

Colorado

Answer 1

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'