Retrieve Rank from SQLite Table

Retrieve Rank from sqlite table

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'

how to get a ranking from a sqlite table

You can use union all :

select distinct r
from (select r1 as r, date, 1 as id
      from t union all
      select r2, date, 2
      from t union all
      select r3, date, 3
      from t
     ) t
order by id, date;

How to Retrieve Rank Based on Total Mark in SQLite Table

Something like this maybe:

with tt(S_id,total) as (
  select S_id, sum(ca1) + sum(ca2) + sum(exam)
    from t
    group by S_id
  union
  select null, 0
  )
  select s.S_id,
         s.total,
         (select count(*)+1
            from tt as r
            where r.total > s.total) as rank
     from tt as s
     where S_id is not null;

How can I get a user's rank/position in SQLite3 with a query?

One option here is to use a correlated subquery to find the dense ranks.

SELECT
    t1.UserName, 
    t1.Coins,
    t2.rank
FROM yourTable t1
INNER JOIN
(
    SELECT
        t1.Coins,
        (SELECT COUNT(*) FROM (SELECT DISTINCT Coins FROM yourTable) t2
         WHERE t2.Coins >= t1.Coins) rank
    FROM (SELECT DISTINCT Coins FROM yourTable) t1
) t2
    ON t1.Coins = t2.Coins;

The above query attempts to handle an edge case where two or more users have the same coin level. It would assign dense ranks as follows:

UserName | Coins | Rank
-----------------------
Cat      |  33   | 1
Matthew  |  18   | 2
Nick     |  12   | 3
Michael  |  12   | 3
Donald   |  12   | 3
Jim      |  10   | 4
Alf      |  10   | 4

no partitioned Rank in SQLite: need a Emulation

You are close. You need a condition on the date and to use >= for the comparison:

select p1.*, 
       (select count(*) 
        from table as p2
        where p2.date = p1.date and
             p2.time >= p1.time
       ) as timeRank
from table as p1;

Strictly speaking, this is closer to a dense_rank(), but the difference is immaterial unless you have duplicate values. If you have ties, then the following should work:

select p1.*, 
       (select 1 + count(*) 
        from table as p2
        where p2.date = p1.date and
             p2.time > p1.time
       ) as timeRank
from table as p1;

find rank with the user details SQL

Why aren't you using window functions? These perform much better and are more powerful:

select brp.*
from (SELECT brp.*, ROW_NUMBER() OVER (ORDER BY progress DESC) as seqnum
      FROM runprogress brp
     ) brp

I'm not 100% sure what you want, but if you want the ranking for runner = 1, then add a where clause:

select brp.*
from (SELECT brp.*, ROW_NUMBER() OVER (ORDER BY progress DESC) as seqnum
      FROM runprogress brp
     ) brp
where runner = 1;

Note: Your method of ranking gives runners with the same progress different rankings, which is why this uses ROW_NUMBER(). If you want ties to have the same score, use either RANK() or DENSE_RANK().

Here is a db<>fiddle.

SQL ranking query to compute ranks and median in sub groups

I suggest doing the computing in your programming language:

for each group:
  for each record_in_group:
    append y to array
  median of array

But if you are stuck with SQLite, you can order each group by y and select the records in the middle like this http://sqlfiddle.com/#!5/d4c68/55/0:

UPDATE: only bigger "median" value is importand for even nr. of rows, so no avg() is needed:

select groups.gid,
  ids.y median
from (
  -- get middle row number in each group (bigger number if even nr. of rows)
  -- note the integer divisions and modulo operator
  select round(x) gid,
    count(*) / 2 + 1 mid_row_right
  from xy_table
  group by round(x)
) groups
join (
  -- for each record get equivalent of
  -- row_number() over(partition by gid order by y)
  select round(a.x) gid,
    a.x,
    a.y,
    count(*) rownr_by_y
  from xy_table a
  left join xy_table b
    on round(a.x) = round (b.x)
    and a.y >= b.y
  group by a.x
) ids on ids.gid = groups.gid
where ids.rownr_by_y = groups.mid_row_right

---

Related Topics