query to display softball standings

[verbob]

I'm not all that certain how to go about this, but here's what I am trying to do:

Firstly, coding in PHP and using MySql DB.

I have a table (called overall) that holds each individual Softball teams stats.

fields are:

1. team (team name)
2. gp (games played)
3. win (# of wins)
4. loss (# of losses)
5. rf (runs for)
6. ra (runs against)
7. pct (win percentage)

I want to query the DB and retrieve the teams in order of their ranking(basically calculate their 'ranking' on-the-fly)

My guess would be something like this:

select * from overall ORDER BY pct desc, win desc, loss asc

the 'rf' and 'ra' fields come into play somehow, though no idea at the moment, but I'll focus on that once I figure out how to get that far...

Anyone have any suggestions?

Thanks!

[tesuji]

Hi

I think it's a good idea to state a convenient example in the form of a data table and you may define "ranking" more precisely. (There are nice ranking functions in OLAP which has been offered by almost all databases since the ANSI SQL1999 standard.)

-- tesu

[verbob]

Ok, I'm not sure what you are getting at...

Heres what I would like the output to look like(18 teams total - not seperated into divisions):

Rank Team Win Loss PCT
-------------------------------------
1 teamname 5 3 0.625
2 teamname 4 4 0.500
..etc...

All the data in the table EXCEPT rank is calculated and entered into the database.

After reading some extremely confusing info on the MySQL " AS rank", I'm really lost!

[verbob]

From what I can understand so far, the MySQl rank simply adds numbers(like 1,2,3..) to an ALREADY sorted query..I see no benefit in this instance as I was planning on doing that in the coding anyway.

[tesuji]

Well, MySQL does not have any olap functionality standardized by ANSI since SQL1999. Even the primitive aggregate function row_number() is missing. This is just another gotcha of that eminently imperfect so-called database. Since the early 2000th there have been thousands postings on forums on how to simulate that aggregate function.

Ok, this is what I would run on almost all other SQL databases:

select row_number() over (order by swin desc, sloss asc) as rank, team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT from overall GROUP BY team;
/*
What should produce suchlike table (I hope so) 
rank team swin sloss pct
-------------------------
1    A     4     1   0.8
2    D     1     1   0.5
3    B     1     2   0.3
4    C     0     2   0.0*/

Double casting is necessary because of integer division. If you don´t need the rank column, you may try this on msql:

select team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT  from overall GROUP BY team order by swin desc, sloss asc;

You may also search the forums on how to simulate row_number(), here is such a try I have found and modified to fit the above listing:

SET @rank=0; SELECT @rank:=@rank+1 AS rank, team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT  from overall GROUP BY team order by swin desc, sloss asc; -- I don't know whether it will work

Attention: above sql statements not tested

-- tesu

[verbob]

Thanks tesu, I'll test and see if this will work for me.