Return Only Max Points - One Query Multiple Tables AGAIN

[crashwave]

Wasn't sure whether or not to unsolve my other question even though it was solved. http://www.phpfreaks.com/forums/mysql-help/one-query-to-find-result-from-multiple-tables/.

"Clubs enter tournament. Each club has 5 players with a rank to play same ranked players from other clubs.

Each player plays match(es) in each tournament and gets a score (0,1,2 points)

There are many tournaments each with many matches."

Model  Here http://picasaweb.google.com/lh/photo/s6b82nLc84H5awZhcy-GIig4DBDpe4IEPAbqDkhUXMw (names not the same as current DB like match2player changed to player2match)

I have

SELECT t.TOURN_ID AS TOURNAMENT, c.CLUB_NAME AS 'CLUB NAME', SUM( p2m.POINTs ) AS POINTS
FROM PLAYER2MATCH p2m
JOIN PLAYER2CLUB p2c ON p2m.P2C_ID = p2c.P2C_ID
JOIN CLUBS c ON p2c.CLUB_ID = c.CLUB_ID
JOIN MATCH2TOURNAMENT m2t ON p2m.M2T_ID = m2t.M2T_ID
JOIN TOURNAMENTS t ON m2t.TOURN_ID = t.TOURN_ID
WHERE t.TOURN_ID = 1
GROUP BY  c.CLUB_ID
ORDER BY POINTS DESC

Which lists the points received by each CLUB_NAME per TOURN_ID input in the where clause.

What I needed (just found out) is to list only the ones with the MAX point for each TOURN_ID

So it would be like this

TOURNID | NAME | POINTS

1            |  A      |  25

2            |  B      |  21

3            |  C      |  23

4            |  D      |  21

.

.

. However many Tourn_ID's there are

 

Any ideas please, Thanks

 

 

[crashwave]

I have made somewhat of a breakthrough

I have it giving me

TOURN_ID | CLUB_NAME | POINT

 

for the highest score/club/tourn. so only the number of tourn is returned not each score for each club in each tourn as before.

But there are times where more than one club gets the high score and the number of rows need to be more than the # of tourn.

TOURN_ID | CLUB_NAME | POINT

1              | Club 6        | 15

2              | Club 3        | 20

2              | Club 4        | 20

3              | Club 1        | 22

4              | Club 2        | 15

4              | Club 5        | 15

4              | Club 6        | 15

5              | Club 4        | 20

 

Here is the query, it is a mess.

SELECT        tournaments.TOURN_ID,  tournaments.CLUB_NAME, tournaments.TOURN_POINTS
FROM            (SELECT TOURNAMENTS.TOURN_ID, CLUBS.CLUB_NAME AS CLUB_NAME, SUM(PLAYER2MATCH.POINTS)  TOURN_POINTS 
FROM PLAYER2MATCH, MATCH2TOURNAMENT, PLAYER2CLUB, TOURNAMENTS, CLUBS 
WHERE PLAYER2MATCH.M2T_ID = MATCH2TOURNAMENT.M2T_ID AND 
PLAYER2MATCH.P2C_ID = PLAYER2CLUB.P2C_ID AND 
MATCH2TOURNAMENT.TOURN_ID = TOURNAMENTS.TOURN_ID AND 
PLAYER2CLUB.CLUB_ID = CLUBS.CLUB_ID 
GROUP BY TOURNAMENTS.TOURN_ID, CLUBS.CLUB_ID
ORDER BY TOURNAMENTS.TOURN_ID , TOURN_POINTS DESC) AS tournaments
GROUP BY tournaments.TOURN_ID

 

Any thoughts PLEASE.

 

[fenway]

Sorry, I don't see a question.

[crashwave]

But there are times where more than one club gets the high score and the number of rows need to be more than the # of tourn.

TOURN_ID | CLUB_NAME | POINT

1              | Club 6        | 15

2              | Club 3        | 20

2              | Club 4        | 20

3              | Club 1        | 22

4              | Club 2        | 15

4              | Club 5        | 15

4              | Club 6        | 15

5              | Club 4        | 20

 

Need to show all highest/'tourn scores like above. How do I make it show up all clubs that got the same highest score.

 

[fenway]

Is that output the desired output, or the sample input?

[crashwave]

Sorry for late update/response... FINALS

The output needed is this

TOURN_ID | CLUB_NAME | POINT

1              | Club 6        | 15

2              | Club 3        | 20

2              | Club 4        | 20

3              | Club 1        | 22

4              | Club 2        | 15

4              | Club 5        | 15

4              | Club 6        | 15

5              | Club 4        | 20

 

The output I currently have

TOURNID | NAME | POINTS

1            |  A      |  25

2            |  B      |  21

3            |  C      |  23

4            |  D      |  21

.

.

. However many Tourn_ID's there are

with this query

SELECT t.TOURN_ID Tournament,  t.CLUB_NAME 'Club Name', t.TOURN_POINTS POINTS
FROM (SELECT t.TOURN_ID, c.CLUB_NAME AS CLUB_NAME, SUM(p2m.POINTS)  TOURN_POINTS, t.TOURN_START 
FROM PLAYER2MATCH p2m
JOIN MATCH2TOURNAMENT m2t ON p2m.M2T_ID =  m2t.M2T_ID  
JOIN PLAYER2CLUB p2c ON p2m.P2C_ID = p2c.P2C_ID  
JOIN TOURNAMENTS t ON m2t.TOURN_ID = t.TOURN_ID  
JOIN CLUBS c ON p2c.CLUB_ID = c.CLUB_ID 
GROUP BY t.TOURN_ID, c.CLUB_ID
ORDER BY t.TOURN_ID , TOURN_POINTS DESC) AS t
WHERE YEAR(t.TOURN_START) = 2010
GROUP BY t.TOURN_ID

 

it only shows 1 score for each tourn. I need all the names with the same high score.

 

since the project is over

here is the model

http://picasaweb.google.com/amommy/Mysql?authkey=Gv1sRgCIe47KrJqf2QpAE#5548404928863439954

here is the db

https://docs.google.com/leaf?id=0BygKF_ts9C6KYjQ1MWRjZTMtMDBkOS00NmM4LThiNzMtZWJjOGY4OTRmNDA1&hl=en&authkey=CNqBjJwL

 

 

Thanks

[fenway]

I'm confused now -- the "project is over"?

[crashwave]

Yes the project assignment is over.

Had to turn it in last week. turned in what I had which that one did what the question wanted but since there were more than one 'name's with a high score it SHOULD HAVE showed them all. I would like to find a way to do it anyway, for later, so I know how/where I messed up.

Does it matter

[fenway]

Well, it matters a bit, since I don't want to do your homework -- I just don't have the time to solve non-real-world issues (no offense).

 

Maybe someone else will step up to the plate.

[crashwave]

Well, it matters a bit, since I don't want to do your homework -- I just don't have the time to solve non-real-world issues (no offense).

 

Maybe someone else will step up to the plate.

I totally understand. I did not want anyone to actually solve my probs. Just needed an idea or somehting what I was doing wrong right. There were many other problems, I would have asked many more questions if that were the case.

Anyway like I said it is done and over with so this is not for a grade, and it may someday be someones real-world-issue