CODEWITHSUNDEEP
mysqllistranking
Jam
Jam

Reputation: 197

mysql get order number

I have the following structure

Country - UserId - Points

840          23          24
840          32          31
840          22          38
840          15          35
840          10          20
250          15          33
724          17          12

etc

I want to get the position of user in the ranking of each country accordin points

I'm using 

select @rownum:=@rownum+1 Num, Country, UserId, points 
from users , (SELECT @rownum:=0) r  
where country=840 order by points DESC ;

I want to get the position of a single user inside his country

In this example, in country 840, if I select user id=23, I'll get position 4

Country - UserId - Points- Order
840         22         38        1
840         15         35        2
840         32         31        3
840         23         24        4
840        10          20        5

Upvotes: 4

Views: 1624

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Try doing:

select * from (
  select @rownum: = @rownum + 1 Num,
        Country,
        UserId,
        points
  from users, (select @rownum: = 0) r
  where country = 840
  order by points desc
) a
where userId = 23

Upvotes: 4

Elon Than
Elon Than

Reputation: 9765

Using your query you'll receive row number in your results so it's not what you want to. Best way is to generate positions and save them to separated column. This way you'll be able to select it easy and there will be no need to recalculate it each time (which is very important).

To do it you can modify your query to update rows instead of selecting it.

Upvotes: 1

Related Questions