Storing rank position from mysql

Question

I was looking for a forum to find a solution to my problem. My problem is that I can’t find out how to maintain the rating position of each tournament that is held. I created two tables and a query that looks like this: competitors (primary key cid int auto_increment, name varchar (25), lastname varchar (25)); comps (compid int auto_increment primary key, tournament int, cid int, points int);

select @rowno:= @rowno+1 as position, rank.* from ( select name,lastname,SUM(points) as pts,group_concat(points) as round FROM (select cid,tournament,points from comps group by cid,tournament order by points)total join competitors c on c.cid = total.cid cross join (select @rowno := 0) r group by total.cid order by pts desc) rank order by pts desc 

Here is the SQLFiddle demo

The thing I want to achieve is that when a user clicks on a competitor’s profile, the positions are displayed for each tournament as follows:

 Name: Competitor One Tournament 1: 1st 100 pts Tournament 2: 2nd 80 pts Tournament 3: 10th 30 pts 

I have grouped the points, but I do not know how to do this with the positions. Is this possible from this query or do I need to create a new table, such as positions (primary key pid int auto_increment, tournament int, cid int, position int), where I insert each position for each participant.

Hope someone understands my problem and can give me some advice or solutions to this problem.

Answer 1

If my understanding were correct, I hope this works for you.

 select *,(SELECT COUNT(*) FROM comps AS cmp2 WHERE cmp1.points < cmp2.points AND cmp1.tournament = cmp2.tournament) + 1 AS position from competitors AS c1 inner join comps AS cmp1 on c1.cid = cmp1.cid order by c1.name,c1.lastname,cmp1.compid, position ASC 

Working Sql Fiddle Here

Answer 2

If you want to get results for each tournament, you can do it as follows:

 select * from competitors inner join comps on competitors.cid = comps.cid order by tournament, points desc 

If you want it to be grouped, the easiest way is to use a subquery to summarize points for each member:

 select *,(select sum(points) from comps where competitors.cid=comps.cid) as points from competitors order by points desc 

And no, you do not need a table with positions, because this is calculated data. The only reason you want to do this is for performance reasons (and update it automatically every x times).