CODEWITHSUNDEEP
phpmysqlmysqliranking
Headpetrol
Headpetrol

Reputation: 111

Storing rank position from mysql

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

Upvotes: 7

Views: 344

Answers (2)

jamseernj
jamseernj

Reputation: 1052

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

Upvotes: 1

Alfons
Alfons

Reputation: 531

If you want the results per tournament you can do it like this:

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

If you want to have it grouped easiest is to use a subquery to sum up the points per competitor: 

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

And no you don't want a table with positions, because thats calculated data. The only reason you'd want to do that is for performance reasons (and update it automatically every x time).

Upvotes: 0

Related Questions