CODEWITHSUNDEEP
mysqlsqlranking
Rahul Agarwal
Rahul Agarwal

Reputation: 935

ranking one column on another column

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?

Upvotes: 7

Views: 407

Answers (4)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this:

select a.userid , a.name minRank, 
       (select name from usertable where userid =a.userid and rank>a.rank 
        order by rank limit 1) nextMinRank
From (Select userid, name ,rank from usertable order by rank) as a
Group by userid, rank

Upvotes: 0

scc
scc

Reputation: 385

This did work for me with same data 

SELECT  N1.USERID AS USERID,N1.NAME AS MIN_RANK, N2.NAME AS NEXT_RANK 
FROM (SELECT USERID,NAME FROM TABLE1 WHERE RANK IN(SELECT  Min(Table1.rank) AS MinOfrank FROM Table1 GROUP BY Table1.UserID))  N1,
(SELECT USERID,NAME FROM TABLE1 WHERE RANK IN (SELECT Min(RANK) AS Expr1 FROM TABLE1 WHERE (TABLE1.rank) Not In (SELECT MIN(RANK) FROM TABLE1 GROUP BY USERID)  GROUP BY USERID))  N2 
WHERE
N1.USERID=N2.USERID;

Hope it helps!!

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Not the most elegent one. But give it a try.

Query:

select b.userid, x.name1, b.name  as name2
from ranks b
join 
(
select a.userid, min(a.rank), a.name as name1
      from ranks a
      group by a.userid
) as x
on x.userid = b.userid
where (b.userid, b.rank, b.name) not in
(
select a.userid, min(a.rank), a.name
      from ranks a
      group by a.userid
)
group by b.userid
;

Results:

USERID  NAME1   NAME2
1       raj     frank
2       liz     neeta

Upvotes: 2

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 2

Related Questions