advice required for ordering, grouping sorting etc

[jmcall10]

Ok I was originally going to ask my questions in stages, however I feel it would be best if I try and give the bigger picture.

 

I have 2 tables:

 

players (pid, name)

gameinfo (gid, name, date, wld)

 

 

The "players" table stores the player id (pid) and the players name (name).

 

The "gameinfo" table stores the gameinfo id (gid) the player name (name) the date(date) and the result (wld) <--- win, lost or drawn

 

So an example of the players table would be:

 

[table][tr][td]1

Bob

2frank

3Joe[/trd]

4Steve

5John

6Mick

7Pat

8Dave

9Andy

10Paul

 

and an example of the gameinfo table would be:

1Bob2008-08-04Won

2Frank2008-08-04Won

3Joe2008-08-04Won

4Steve2008-08-04Won

5John2008-08-04Won

6Mick2008-08-04Lost

7Pat2008-08-04Lost

8Dave2008-08-04Lost

9Andy2008-08-04Lost

10Paul2008-08-04Lost

11Bob2008-08-11Lost

12Frank2008-08-11Lost

13Joe2008-08-11Lost

14Steve2008-08-11Lost

15John2008-08-11Lost

16Mick2008-08-11Won

17Pat2008-08-11Won

18Dave2008-08-11Won

19Andy2008-08-11Won

20Paul2008-08-11Won

21Bob2008-08-18Draw

22Frank2008-08-18Draw

23Joe2008-08-18Draw

24Steve2008-08-18Draw

25John2008-08-18Draw

26Mick2008-08-18Draw

27Pat2008-08-18Draw

28Dave2008-08-18Draw

29Andy2008-08-18Draw

30Paul2008-08-18Draw

 

 

What I want is a query to order the gameinfo data so that it shows who has the highest win rate.

 

So not who has won the most games, but who has the highest win rate percentage of the amount of times they have played. Does that make sense?

 

It might even be that I have designed these tables wrong from the start.

 

Any assistance would be great

 

Thanks in advance

 

jmcall10

 

[adam84]

Well the first thing that I would do is change the 'gameinfo' table, instead of having the column 'name', I would have 'playerID' which stores the playerID instead of their name. Because as of now if there were two different Frank's playing, you wouldnt be able to tell which Frank was which. But by storing the playerID, each player would have their own ID.

 

Also I instead of having 'Win', 'Lost', 'Draw;.

I would use a numeric value like 1 -> Won, 0 -> Draw, -1 -> Lost.

 

This would return all the players with their total wins. Sorted by which player has the most wins.

SELECT playerID, count(playerID) AS wins FROM gameinfo WHERE wld = 1 GROUP BY playerID ORDER BY count(playerID) DESC 

* something like this

[Barand]

see

 

http://www.phpfreaks.com/forums/index.php/topic,210124.0.html