CODEWITHSUNDEEP
phpmysqlsql
Henry1988
Henry1988

Reputation: 83

Ordering standings football [soccer] positions by direct matches

I have a problem trying to apply rules about direct matches in a football[soccer] app. I have read this tread and it was very heplful on creating the standing positions table by the points criteria, difference and scored goals.

But i would like to know if is possible to order the teams position by direct matches:

look this positions table:

Pos Team           Pld  W   D   L   F   A   GD  Pts
1   FC Barcelona    5   2   3   0   8   5   3   9
2   **Inter Milan** 6   2   2   2   11  10  1   8
3   *Real Madrid*   6   2   2   2   8   8   0   8
4   AC Milan        5   0   3   2   8   12  -4  3

As you may see Inter Milan and Real Madrid are tied by points, and the Inter is heading real madrid because its goal difference. The result that i want to get is this :

Pos Team                Pld  W   D   L   F   A   GD  Pts
    1   FC Barcelona    5   2   3   0   8   5   3   9
    2   **Real Madrid** 6   2   2   2   8   8   0   8
    3   *Inter Milan*   6   2   2   2   11  10  1   8
    4   AC Milan        5   0   3   2   8   12  -4  3

Notice that in this time the real madrid is in front the inter milan because it won the two direct matches between them.

i have a table for teams and other for the results.

I would like to achive this using a query in mysql if is possible. Or maybe it would be better if i do this ordering on the server side (PHP).

Thanks any help would be appreciated.

Upvotes: 3

Views: 1997

Answers (2)

Ganesh Narayanan
Ganesh Narayanan

Reputation: 1

create temporary in a stored procedure and call to procedure...

create temporary table tab1 (position int not null auto_increment ,
                      team_name varchar(200),
                      points int,
                      goal_pt int,
                      primary key(position));

insert into tab1(team_name, 
                 points,
                 goal_pt) 
    select team_name,
           points,
           goal_pt 
    from team     
    order by points desc,
             goal_pt desc ;

Upvotes: 0

Toote
Toote

Reputation: 3413

It is impossible to efficiently do what you request in a single query that would return the results you ask for and sort the ties in points with that criteria.

The reasoning is simple: lets assume that you could get a column in your query that would provide or help with the kind of sorting you want. That is to say, it would order teams that are tied in points according to which one has more victories over the others (as this is very likely to happen to more than 2 teams). To make that calculation by hand you would need a double-entry table that shows the amount of matches won between those teams as follows:

       | TeamA | TeamB | TeamC
 ------------------------------
 TeamA |     0 |   XAB |   XAC
 TeamB |   XBA |     0 |   XBC
 TeamC |   XCA |   XCB |     0

So you would just add up each column row and sorting in descending order would provide you the needed data.

The problem is that you don't know which teams are tied before you actually get the data. So creating that column for the general case would mean you need to create the whole table of every team against every team (which is no small task); and then you need to add the logic to the query to only add up the columns of a team against those that are tied with it in points... for which you need the original result set (that you should be creating with the same query anyhow).

It may be possible to get that information in a single query, but it will surely be way too heavy on the DB. You're better off adding that logic in code afterwards getting the data you know you will need (getting the amount of games won by TeamA against TeamB or TeamC is not too complicated). You would still need to be careful about how you build that query and how many you run; after all, during the first few games of a league you will have lots of teams tied up against each other so getting the data will effectively be the same as building the whole double-entry table I used as an example before for all teams against all teams.

Upvotes: 3

Related Questions