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'

Answer 2

Andomar's answer is a good one, and it should almost certainly remain the selected answer for this question. That said ...

I found that a complex query I was running quickly became unwieldy when I tried to shoehorn it into Andomar's solution, so out of desperation, I tried using something like the following code:

CREATE TABLE DoughnutShopCountsByHood AS 
SELECT Neighborhood, COUNT(*) AS DoughnutShopCount FROM 
(  <<crazy-set-of-painful-subqueries-removed>>  )
GROUP BY Neighborhood ORDER BY DoughnutShopCount DESC;

The important part is the "CREATE TABLE ... AS" part in the first line. I had planned for this to be the first of a few steps, but at least in Firefox's SQLite Manager, I was pleasantly surprised to find that when I dumped my ridiculously long query into a new table, the RDBMS simply added an index column automatically. This column doubles nicely as a "rank" column.

I realize this is a really old question, so this answer probably won't get any upvotes, but I'm posting it in case my personal experience can help someone else with a similar challenge.

Thanks again to Andomar for the original answer -- I imagine it's the most helpful one for most people.