mysql sort table and get row position mysql

Question

I have the following table structure for a table Player

Table Player {  
Long playerID;  
Long points;  
Long rank;  
}

Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.

UPDATE:

I'm using hibernate using the query suggested as a native query. Hibernate does not like using variables, especially the ':'. Does anyone know of any workarounds? Either by not using variables or working around hibernate's limitation in this case by using HQL?

this serves your purpose http://stackoverflow.com/questions/11963818/row-number-equivalent-in-mysql-for-inserting — sarwar026, Jul 07 '13 at 06:30

Dinup Kandel · Answer 1 · 2013-07-07T06:57:54.953

0

select  @a:=@a+1 position,
      id, 
      sum(item_1+item_2+item_3) as item_sum 
 from yourtable , (SELECT @a:= 0) AS a
 group by id 
 order by item_sum desc;

you can also try this

SET @position=0;
SELECT   @position:= @position+1 AS position,   
          id, 
          sum(item_1+item_2+item_3) as item_sum 
     from yourtable
     group by id 
     order by item_sum desc;

or try this one.......

SELECT @rn:=@rn+1 AS position, item_sum , id
FROM (
  select 
      id, 
      sum(item_1+item_2+item_3) as item_sum 
 from yourtable 
 group by id 
 order by item_sum desc
) t1, (SELECT @rn:=0) t2;

edited Jul 07 '13 at 06:57

answered Jul 07 '13 at 06:29

Dinup Kandel

2,457
4
21
38

The positions are not in order – user2310422 Jul 07 '13 at 06:36
what happens with the second query then? – Dinup Kandel Jul 07 '13 at 06:37
@DinupKandel: You shouldn't copy someone's answer in the same question at least! – sarwar026 Jul 07 '13 at 06:38
I just tried the second query, it has the same results. The positions are not in order – user2310422 Jul 07 '13 at 06:38
@sarwar026 i havent copied you answer. see the time gap between the answer you have given and mine. you might be mistaken. – Dinup Kandel Jul 07 '13 at 06:39
@DinupKandel: I want to mean your second query not the first one. – sarwar026 Jul 07 '13 at 06:40
@user2310422 this might be because you have done order by `item_sum` – Dinup Kandel Jul 07 '13 at 06:40
That's the thing, I want to order them by item_sum THEN get their position – user2310422 Jul 07 '13 at 06:41
@sarwar026 i took the hints from http://stackoverflow.com/questions/11963818/row-number-equivalent-in-mysql-for-inserting . but i have not copied your answer. you too might have seen the answer from there too. – Dinup Kandel Jul 07 '13 at 06:42
That's the thing, I want to order them by item_sum THEN get their position – user2310422 Jul 07 '13 at 06:43
@sarwar026 at the end i can say you are not the only one who writes query like this... – Rajeev Ranjan Jul 07 '13 at 06:46
This is not a place to fight. You guys acts like a kid – user2310422 Jul 07 '13 at 06:49
@RajeevRanjan: if you see the comments of question, You will see I first post the original question and then when I see the answer this user post didn't work well, then I guess, I can post the answer here too. You understand my point? Thanks – sarwar026 Jul 07 '13 at 06:49
This is not a place to fight. You guys acts like a kid – user2310422 Jul 07 '13 at 06:49
1

@RajeevRanjan you too join the group. I know the first sql trick by myself as i have implemented this before. and there is no wrong on writing this answer as it cause the knowledge to share. – Dinup Kandel Jul 07 '13 at 06:49
1

At least make some sense – user2310422 Jul 07 '13 at 06:52
@user2310422 i will modify my answer just check to see if it is correct. – Dinup Kandel Jul 07 '13 at 06:54
@user2310422 do you get through you problem? – Dinup Kandel Jul 07 '13 at 07:20

Rajeev Ranjan · Answer 2 · 2013-07-07T06:54:30.763

0

// increment num by one for each result row @num:=@num+1 with aliasname position and define it initially with 0,or by what to want to be..

select @num:=@num+1 position,
  id, 
  sum(item_1+item_2+item_3) as item_sum 
  from yourtable,(SELECT @num:=0) r
group by id 
order by item_sum desc;

edited Jul 07 '13 at 06:54

answered Jul 07 '13 at 06:38

Rajeev Ranjan

4,152
3
28
41

mysql sort table and get row position mysql

2 Answers2