ranking one column on another column

Question

I have a table of the form:

userid  rank  name
-------------------
1       4     raj
1       90    frank
1       100   neeta
1       123   liz
2       2     liz
2       20    neeta
2       23    frank
2       222   raj

I need to convert this into

userid  min_rank  next_min_rank
-------------------------------
1       raj       frank
2       liz       neeta

I have been searching the net for this issue for a long time but could not find a solution.

Could you please help?

Answer 1

This query will select the minimun rank, and the next rank for every user:

select s.userid, s.min_rank, min(users.rank) as next_rank
from (
  select userid, min(rank) as min_rank
  from users
  group by userid) s left join users
  on users.userid = s.userid and s.min_rank<users.rank
group by s.userid

ant this will show the names:

select t.userid, t1.name, t2.name
from (
  select s.userid, s.min_rank, min(users.rank) as next_rank
  from (
    select userid, min(rank) as min_rank
    from users
    group by userid) s left join users
    on users.userid = s.userid and s.min_rank<users.rank
  group by s.userid ) t
  left join users t1 on t.userid=t1.userid and t.min_rank=t1.rank
  left join users t2 on t.userid=t2.userid and t.next_rank=t2.rank

The result is:

1  raj  frank
2  liz  neeta

If there could be more than one user with the same rank, it depends on how you want to handle the situation, you might just add this:

group by t.userid

to show just one of them. Maybe you could also use GROUP_CONCAT(t1.name), GROUP_CONACT(t2.name) on the select.