Storing rank position from mysql

Question

I have searched the forum for finding the solution for my problem. My problem is that I can´t find out how to save a ranking position of each tournament that is held. I have created two tables and a query that look like this: competitors (cid int auto_increment primary key, name varchar(25), lastname varchar(25)); comps (compid int auto_increment primary key,tournement 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 SQLFiddle demo

The thing I want to achieve is that when a user has clicked on a competitor profile the positions are shown for each tournament like this:

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 have no clue how to do that with positions. Is this possible from this query or do I have to create a new table like positions (pid int auto_increment primary key, tournament int, cid int, position int) where I insert each position for each competitor.

Hopefully someone understands my problem and could give me some hints or solutions to this problem

Answer 1

If my understanding was right then I hope this will work 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