FIRST Ranking Software

[Rick]

Has anyone duplicated in excel or other database program, the way FIRST ranks teams during competition? With full QP and RP support? A search returned nothing. I am looking for a way to dynamically rank in the stands and to also simulate "what if" situations for various teams during competition. Any help would be greatly appreciated.

[AdamHeard]

If you know some excel, creating a program where you simply put in the info about matches (team numbers, scores) and it would tally QP and RP then sort them, wouldn't be too difficult.

[Michael Hill]

I did something similar years ago. Check it out: http://www.chiefdelphi.com/media/papers/1603

Modify it, cannibalize it, I don't really care.

[EricH]

I'm with Adam. It took me about an hour, and half of that was trying to cram 3 "if" statements into one formula. (I gave up and put them in three.)

A little more time and knowhow,and I'd have a fully-functioning one, complete with interface to make things automatic (which I currently don't know how to do, hence the "more knowhow"). Right now, you'd need a lot of sheets.

If anyone wants it, email me.

[Scott L.]

I believe I am very close to getting a system working in SQL to do rankings. I f anyone knows how to find the minimum score between two colums it would be done.

[Michael Hill]

Why don't you just find the minimum of each column individually, then compare the results?

[Joe Ross]

Here's a few:

http://www.chiefdelphi.com/media/papers/1513
http://www.chiefdelphi.com/media/papers/1656
http://www.chiefdelphi.com/media/papers/1661

[Scott L.]

I got my database(SQL) working, might organize wrong, but thats easily fixed by changing the order in which the 'Order By' statement is formed.

[Rick]

Thanks for the help everyone!

[ATannahill]

Scott, can I see a copy of it?

[Scott L.]

Originally Posted by rtfgnow

Scott, can I see a copy of it?

http://asp.shinraikon.com/duelvii/rank.aspx Here is the database in use.

[Nibbles]

Five table SQL join. Fun. (Four is all you need, actually, and it would be six if I only knew $eventid but not $season) This database is structured such that a table can contain data for multiple seasons and events, $eventid and $season are substituted for the database-specific event ID and year, respectively. It what I use for my scouting program to update team rankings.

The IF function is specific to MySQL, any other database will need to replace it with a standard IF/ELSE or CASE statment.

SELECT
 t.teamnum,
 SUM(IF(a.score>o.score,1,0)) AS won,
 SUM(IF(a.score<o.score,1,0)) AS loss,
 SUM(IF(a.score=o.score,1,0)) AS tie,
 SUM(CASE
   WHEN a.score>o.score THEN 2
   WHEN a.score=o.score THEN 1
   WHEN a.score<o.score THEN 0
 END) AS qualify,
 SUM(CASE
   WHEN a.score>=o.score THEN a.points
   WHEN a.score<o.score THEN o.score
 END)/SUM(1) AS ranking,
 SUM(1) AS matches
FROM
 team t LEFT OUTER JOIN team_event u ON t.teamnum=u.teamnum,
 team_match m, alliance a, alliance o
WHERE m.surrogate=0 AND m.disqualified=0
 AND t.teamnum=m.teamnum
 AND a.matchid=m.matchid
 AND o.matchid=m.matchid
 AND a.alliance=m.alliance
 AND o.alliance!=m.alliance
 AND t.season=$season
 AND u.eventid=$eventid
 AND m.eventid=u.eventid
 AND a.eventid=u.eventid
 AND o.eventid=u.eventid
GROUP BY t.teamnum