Mysql - How to get line number after order?

Question

Let's say I have a table with the following columns:

---

P_ID

identifier

Points

---

Say these columns have over 5,000 records. So we have users with glasses. Each user has a unique line for his point record. Imagine that each user can earn points on a website by clicking somewhere. When they click, I update the database with the points they receive.

So, we have a table with over 5,000 records of people who have points, right? Now I would like to order them by their points (in descending order), so the user with the largest point will be at the top of the page if I run a MySQL query.

I could do this simply by running a query like this:

SELECT `p_id` FROM `point_table` ORDER BY `points` DESC 

This query will give me all records in descending order by points.

Ok, here my problem comes , now (when it is ordered ). I would like to display each user they are on. Therefore, I would like to give each user something like this: "You are 623 out of 5374 users." The problem is that I can not specify the number "623".

I would like to run a query that sort the table by points , should “search” or count the line number, where their records are and how to return this value to me.

Can someone help me how to build a query for this? That would be a great help. Thanks.

Answer 1

This answer should work for you:

 SET @rank=0; SELECT @rank: =@rank +1 AS rank, p_id FROM point_table ORDER BY points DESC; 

Update: You can also consider calculating the rank when updating points and saving it in an additional column in the same table. Thus, you can also select one user and find out his rank. It depends on your use cases, which makes more sense and works better.

Update: The final solution that we developed in the comments looked like this:

 SELECT rank, p_id FROM (SELECT @rank: =@rank +1 AS rank, p_id, userid FROM point_table, (SELECT @rank := 0) r ORDER BY points DESC ) t WHERE userid = intval($sessionuserid); 

Answer 2

Line number after ordering by

 SELECT ( @rank: =@rank + 1) AS rank, m.* from ( SELECT a.p_id, a.userid FROM (SELECT @rank := 0) r, point_table a ORDER BY a.points DESC ) m 

Answer 3

Add a new column for the item in the table. Run a cron job regularly, which retrieves all the rows in the table, sorted by points, and then updates the table with positions in the while loop.