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.:D

[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. :D

[Rick]

Thanks for the help everyone!

[ATannahill]

Scott, can I see a copy of it?

[Scott L.]

Scott, can I see a copy of it?

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

[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 (http://team498.org/498scouting) 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