CODEWITHSUNDEEP
phpmysql
Derfder
Derfder

Reputation: 3324

How to order database records based on these criteria?

I have a table called matches with these 4 fields (all are integers):

1.id
2.team1
3.team2
4.winner_team

Some example records so you can see how it look like:

id | team1 | team2 | winner_team
--------------------------------
1  | 3     | 15    | 3
--------------------------------
2  | 20    | 8     | 8
--------------------------------
3  | 18    | 11    | 18
--------------------------------
4  | 8     | 7     | 8
--------------------------------
...

Now, I need to order the table based on numbers of wins by the team. E.g. In the example you see that the number of winner_team no. 8 is 2 (in my real table there are many more records so the winner_team can have e.g. 10 or more occurrences).

Basically, I want to have Top 20 teams from best to the worst based on the number of wins which represent how many times the team is as winner_team. If it is team1 or team2 doesn't matter at all.

I fink I need to use group_by or something like that but I don't know how to do that.

Could somebody help me with this with the mysql select and php code for this case?

Thanks in advance.

Upvotes: 1

Views: 72

Answers (2)

Peter M
Peter M

Reputation: 1059

I think this is what you need http://sqlfiddle.com/#!2/4cc3b/3

Basicly you use the MySQL functions COUNT() and GROUP BY in order to get your result.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select winner_team, count(id) as wins
from your_table
group by winner_team
order by wins desc
limit 20

Upvotes: 3

Related Questions