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
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