Return the corresponding columns of the MAX function in MySQL

Question

I have a table that collects points set by players in a bowling center. Each line contains information about which player played the game, what league he played, date, score of one game, number of the strip, etc.

What I'm trying to do is get who played (and in which league and on what date ... Basically a whole line) the best series (three games) on each strip.

What am i still

SELECT PlayerID, LaneNumber, MAX(Series) FROM (SELECT Season, LeagueName, LaneNumber, WeekNumber, PlayerID, Date, SUM(Score) AS Series FROM Scores GROUP BY Season, LeagueName, WeekNumber, PlayerID) GROUP BY LaneNumber 

This works, since I get the best three games for each separate band, which is actually what I want, but another field containing the PlayerID is not really correct.

In my table, the best score for number 24 (obtained from SUM (Score) and GROUP BY Season, LeagueName, WeekNumber, PlayerID) is 848 and was played by the player with player ID 36.

Instead, I get Lane 24 with 848 (which is true), but the returned PlayedID is 3166. The same thing happens on every single strip. (As in, I get the identifiers of players that are clearly mistaken. And if I had other columns on the first choice, they are also wrong)

Answer 1

As @Jon noted, you need to remove those elements that are NOT applicable to a particular person. Then, since @Ord had the closest sample, it would be better to pre-query the results in a separate table (not temporary, since MySQL will strangle it, trying to query itself in a self-join in the second query).

So, for me (being a bowler in a league a few years ago), and your content covering all leagues, at the same time, there will never be two different leagues in the same lane, however, for the whole evening, you could have different leagues starting different times ... 6-8: 30, 8: 45-11, for example ... so grouping by league and date will work. However, you need the player to be part of the group in order to get the corresponding SUM () values.

To help clarify the answers, suppose I have the following data. This data will represent only one streak, one week, one season, but two leagues and 3 players per league (for the sole purpose of showing results and restricting content here).

 League Player Score L1 1 223 L1 1 218 L1 1 204 L1 2 187 L1 2 201 L1 2 189 L1 3 148 L1 3 152 L1 3 158 L2 4 189 L2 4 195 L2 4 192 L2 5 182 L2 5 199 L2 5 209 L2 6 228 L2 6 234 L2 6 218 CREATE TABLE SeriesScores2 SELECT Season, LeagueName, LaneNumber, WeekNumber, PlayerID, SUM(Score) AS Series FROM Scores GROUP BY Season, LeagueName, LaneNumber, WeekNumber, PlayerID; 

The first request (above) will create will create a series for all players during all weeks, all leagues, etc. Suppose now that I added in the regular season, strip, week too

 Season League Lane Week Player Series 1 L1 1 1 1 645 1 L1 1 1 2 577 1 L1 1 1 3 458 1 L2 1 1 4 576 1 L2 1 1 5 590 1 L2 1 1 6 680 

This gives us the predecessor of the definition of max (), otherwise we would have to duplicate the request internally and externally, which would complicate this preliminary aggregation.

Now, the above permanent table (can be deleted AFTER you get the results), request FIRST (PreQuery) for the maximum score PER LEAGUE PER LANE ... Ex :, its common that the men's league tends to have higher series scores than women ... similar to different age groups. Therefore, the Men League Lane 1 highest score and the League of Women's League 1 highest score, etc. The highest result is usually determined by one week of the entire season, and not by the highest series per lane every week.

Now the nickname "ss" PreQuery is only in the season, league, lane and maximum series. Once THIS is known, join the series to get WHO, to get the highest score in the indicated band and to get who and what week it came.

 select ss.season, ss.leaguename, ss.lanenumber, ss.highestSeries, ss2.PlayerID, ss2.WeekNumber from ( select season, leaguename, lanenumber, max( series ) highestSeries from SeriesScores2 group by season, leaguename, lanenumber ) ss join SeriesScores2 ss2 on ss.Season = ss2.Season and ss.LeagueName = ss2.LeagueName and ss.LaneNumber = ss2.LaneNumber and ss.HighestSeries = ss2.Series 

Now from the above request ... let it break. If you take the internal "ss" prequery

  ( select season, leaguename, lanenumber, max( series ) highestSeries from SeriesScores2 group by season, leaguename, lanenumber ) ss 

We will get the highest scores for the league (for example: Men's League vs Women's League in the same week, same night, in the same strip, and we find (below), just max, but we don’t have WHO or in which week, only the highest bowling series, regardless of the week or person, so IT becomes the backbone of JOIN back to the SeriesScores2 pre-aggregated table, but here we have the highest score series to make sure we find the right person

 Season League Lane HighestSeries 1 L1 1 645 1 L2 1 680 To refresh preaggregation Season League Lane Week Player Series 1 L1 1 1 1 645 <-- Join finds THIS entry League 1 1 L1 1 1 2 577 1 L1 1 1 3 458 1 L2 1 1 4 576 1 L2 1 1 5 590 1 L2 1 1 6 680 <-- Join finds THIS entry League 2 

So, my original queries really worked when I tested them before publishing. I don't know what your hiccup was if the column name is not correct or something like that. As with the Date column, I was not interested because you had an available number of weeks that would correspond to a bowling week and in any case be a 1: 1 ratio to date. A date column could be added to SeriesScores2 pre-aggregation and pulled together when the person identifier and week are obtained. (if the league is not many times a week, THEN you will need an explicit date).

We hope this clarifies your questions / comments.

Answer 2

You violate the semantics of GROUP BY .

When using GROUP BY it only makes sense for SELECT columns that you have grouped using (e.g., LaneNumber ) and the aggregate functions of other columns (e.g., MAX(Series) ). It makes no sense to choose anything else (in your case, PlayerID ), because you will not specify which player identifier you want to use among those who have the same LaneNumber .

Unfortunately, MySql by default will allow you to do this without reporting an error, and will return any value that it selected for the column causing the violation. In your case, this means that you return the player identifier "randomly", selected among those that are included in the specified grouping.

You also do this in an internal query where you select LaneNumber , WeekNumber and Date .

Decision

The request needs to be rewritten, but first you need to carefully indicate which results you want to get. Do you need the best player and relevant data for each series (and any band)? For each series and strip separately? The answer to this question will determine what you need, GROUP BY , and what the query will look like in the future.

Answer 3

Have a look here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

He may have gotten confused trying to do all this in a single query, but basically you want to generate your series data like you did:

 SELECT Season, LeagueName, LaneNumber, WeekNumber, PlayerID, Date, SUM(Score) AS Series FROM Scores GROUP BY Season, LeagueName, WeekNumber, PlayerID 

Then, instead of getting the maximum series values ​​from this table, you need to add a sentence: WHERE Series= , and then to get the correct value, you need to make one more choice, where you get max (Series), where LaneNumber is the same in both tables. I would encode it for you, but I'm not sure enough about my MySQL capabilities!

Answer 4

Ok, trying to write MySQL code that I was thinking about (I could not resist ...):

 CREATE TEMPORARY TABLE SeriesScores SELECT Season, LeagueName, LaneNumber, WeekNumber, PlayerID, SUM(Score) AS Series FROM Scores GROUP BY Season, LeagueName, WeekNumber, PlayerID; 

This bit just gets points for each series, as indicated in your own MySQL code. The only difference is that I do not select Date, because since we do not group it, its value will be random. Then:

 SELECT PlayerID, LaneNumber, Series FROM SeriesScores s1 WHERE Series=(SELECT MAX(s2.Series) FROM SeriesScores s2 WHERE s1.LaneNumber = s2.LaneNumber); 

This bit simply selects what you need from SeriesScores, and only considers lines where the series rating is the maximum for this band.

Does this work for you?