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

Garden

I realize you provided a simplified example--but store the date or year of birth, not the age per se, and calculate age. Don't store the age itself. If I hadn't seen this done quite a few times I wouldn't be writing this comment. – Garrulous

Good point, ages change don't they? – Garden

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'

Mazza

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.

Allista

Answer 3

SQLite has a RANK() function

SELECT
    *,
    RANK () OVER ( 
        ORDER BY age DESC
    ) age_rank
FROM
    yourtable

This would return all the entries with a new 3rd row denoting their age rank

in my testing, this also orders the results by the specified order in RANK()

keep in mind that any WHERE clauses you add will affect the ranking, in order to get the rank of one element, you need some nested statements

SELECT 
    * 
FROM (
    SELECT
        *,
        RANK () OVER ( 
            ORDER BY age DESC
        ) age_rank
    FROM
        yourtable
) 
WHERE 
    name = "Zek";

You can also get the nth oldest by doing nested statements

SELECT 
    * 
FROM (
    SELECT
        *,
        RANK () OVER ( 
            ORDER BY age DESC
        ) age_rank
    FROM
        yourtable
) 
WHERE 
    age_rank = 2;

One thing that might matter that i found in my testing is that all values that are the same have the same rank. i personally think this is good but it might matter

RANK() OVER (
    PARTITION BY <expression1>[{,<expression2>...}]
    ORDER BY <expression1> [ASC|DESC], [{,<expression1>...}]
)

pretty good tutorial i found

reference to it in official docs though does not explain how it works

of course the """fun""" of sqlite is that every implementation is different, though its in the official docs so i cant imagine it would be that rare

Middlemost