Complicated football league Dynamic Ordering in MySQL?

Posted by Dan on Stack Overflow See other posts from Stack Overflow or by Dan
Published on 2010-05-20T17:22:29Z Indexed on 2010/05/20 17:30 UTC
Read the original article Hit count: 312

Filed under:

mysql

|

ordering

|

php

|

sports

I have a table 'games' for a football league as follows:

date    home_team_id    away_team_id    home_score      away_score
 -          1                 2              6             21
 -          3                 1              7             19

I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then points for if poss)?

--

I have this query which works fine when I have a $team_id but of cause then I can only do 1 team at a time, and that doesn't allow for ordering at query level

((SELECT COUNT(*) FROM `games` WHERE ((`home_score` > `away_score`) AND `home_team_id` = '.$team_id.')) + 
(SELECT COUNT(*) FROM `games` WHERE ((`home_score` < `away_score`) AND `away_team_id` = '.$team_id.'))) AS `wins`

I wonder if i can use this with some form of GROUP, or mySQL can know the $team_id itself? I've also tried some multiple JOINs with the 'team' table but they didn't work either.

Thanks,

Dan

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about ordering

Scala 2.8 TreeMap and custom Ordering

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm switching from scala 2.7 and ordered to scala 2.8 and using ordering. It looks quite straight forward but I was wondering could I make it a little less verbose. For example: scala> case class A(i: Int) defined class A scala> object A extends Ordering[A] { def compare(o1: A, o2: A) = o1… >>> More
POP Culture

as seen on Oracle Blogs - Search for 'Oracle Blogs'
When we hear the word POP, we normally think of a soft drink, or a soda, while for others, it might be their favourite kind of music. In my case, it's the sound my knee makes when I bend down. Within Oracle though, when we talk about POP, we are referring to the Partner Ordering Portal. The Partner… >>> More
Questions regarding ordering of catch statements in catch block - compiler specific or language stan

as seen on Stack Overflow - Search for 'Stack Overflow'
I am currently using Visual Studio Express C++ 2008, and have some questions about catch block ordering. Unfortunately, I could not find the answer on the internet so I am posing these questions to the experts. I notice that unless catch (...) is placed at the end of a catch block, the compilation… >>> More
Control vertical source ordering with 960.gs?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I've understood it's possible to control the vertical (columnwise) source ordering with the 960 Grid System. However, is it possible to do something similar vertically? If not, does anyone know of any grid system that handles source ordering vertically? Thanks, Jens >>> More
Mac OS X software always order files alphabetically rather than by type.

as seen on Super User - Search for 'Super User'
I have noticed many Mac applications sort the files alphabetically rather than by type. A good example would be Coda by panic.com. The files in the file menu are organized alphabetically. I requested for them to add the feature to organize files by type, and they've said that it's a Finder thing… >>> More