How to combine GROUP BY and MAX in mysql to get the full record with the max value of a field?

Question

I have a table 'wordstyped' containing 'idBook', 'guidUser', 'bookmarkLetter', 'letter' and 'attemptWrong'.

idBook  guidUser  bookmarkLetter  letter  attemptWrong
------------------------------------------------------
1       1         100             a       2
1       1         100             a       3
1       1         101             b       6
2       2         101             b       2
2       2         101             b       3
2       2         152             d       7
3       3         153             e       2

I want to select all the record with all their fields containing the max number of 'attemptWrong', but distinct triplets 'idBook', 'guidUser' and 'bookmarkLetter'. I thought I could reach this result with correct use of GROUP BY and MAX operators. The desired result is:

idBook  guidUser  bookmarkLetter  letter  attemptWrong
------------------------------------------------------
1       1         100             a       3
1       1         101             b       6
2       2         101             b       3
2       2         152             d       7
3       3         153             e       2

I can get the max attempt right with this query

SELECT *,MAX(attemptWrong) as maxAttemptWrong FROM wordstyped GROUP BY idBook, guidUser, bookmarkLetter

but it returns

idBook  guidUser  bookmarkLetter  letter  attemptWrong  maxAttemptWrong
-----------------------------------------------------------------------
1       1         100             a       2             3
1       1         101             b       6             6
2       2         101             b       2             3
2       2         152             d       7             7
3       3         153             e       2             2

like in this fiddle http://sqlfiddle.com/#!9/135cf9/1

So it returns the correct maxAttemptWrong, but not the correct record. What am i missing?

Tim Biegeleisen · Accepted Answer · 2018-07-18T11:28:05.253

2

I believe you will need some sort of subquery to do this, even if you were using analytic functions. Using a pre MySQL 8 approach, we can join your wordstyped table to a subquery which finds the max wrong attempts for each bookmark letter. This restricts the original table to only the matching rows which you want in your final output.

SELECT w1.*
FROM wordstyped w1
INNER JOIN
(
    SELECT bookmarkLetter, guidUser, MAX(attemptWrong) AS maxAttemptWrong
    FROM wordstyped
    GROUP BY bookmarkLetter, guidUser
) w2
    ON w1.bookmarkLetter = w2.bookmarkLetter AND
       w1.guidUser = w2.guidUser AND
       w1.attemptWrong = w2.maxAttemptWrong;

edited Jul 18 '18 at 11:28

answered Jul 18 '18 at 11:20

Tim Biegeleisen

502,043
27
286
360

1

You will need group by clause as `GROUP BY idBook, guidUser, bookmarkLetter` as he mentioned on distinct triplets. Also join condition to be modified accordingly. – skelwa Jul 18 '18 at 11:26
Cool! 2 , 2, 101, b, 3 is missing, unfortunately! – Sasha Grievus Jul 18 '18 at 11:27
@SashaGrievus Your question is not too clear and is cluttered. It looks like you want the max record for every combination of `guidUser` _and_ `bookmarkLetter`. My updated query handles this. – Tim Biegeleisen Jul 18 '18 at 11:28
Yes, I want the max record for every combination of 'idBook' and 'guidUser' and 'bookmarkLetter, that's the reason of the initial group by – Sasha Grievus Jul 18 '18 at 11:32
You don't have to use a sub-query - have a google for the "max concat trick" – symcbean Jul 18 '18 at 11:40
@symcbean I've never heard of this, and it sounds like a hack. – Tim Biegeleisen Jul 18 '18 at 11:42
Go read up and understand how it works, then have a think about why it can be significantly more efficient than a sub-query (not always the case) – symcbean Jul 18 '18 at 11:45
You should provide a link here. Searching for `max concat trick` did not bring up anything related to SQL join queries. – Tim Biegeleisen Jul 18 '18 at 11:46
Thank you for the answer. I was hoping it could be done without subquery for this way I am not able to create a View, but I can use it anyway. – Sasha Grievus Jul 18 '18 at 12:06
@SashaGrievus if you checked the answers on the duplicate question link I provided, you would find solutions that do not involve a subquery. – Shadow Jul 18 '18 at 12:11
@TimBiegeleisen the duplicate question does have the group_concat() trick and other tricky solutions.Rather than answering this type of question over and over again, perhaps you should link to the duplicate topic yourself... – Shadow Jul 18 '18 at 12:15

Strawberry · Answer 2 · 2018-07-18T14:33:06.443

SELECT x.* 
  FROM wordstyped x
  JOIN 
     ( SELECT idbook
            , guiduser
            , bookmarkletter
            , MAX(attemptwrong) attemptwrong
         FROM wordstyped 
        GROUP
           BY idbook
            , guiduser
            , bookmarkletter
     ) y
    ON y.idbook = x.idbook
   AND y.guiduser = x.guiduser
   AND y.bookmarkletter = x.bookmarkletter
   AND y.attemptwrong = x.attemptwrong

http://sqlfiddle.com/#!9/135cf9/9

To my mind, Views are almost completely useless in MySQL - but if you must have one, then you can re-write the above this way...

SELECT x.*
  FROM wordstyped x
  LEFT 
  JOIN wordstyped y
    ON y.idbook = x.idbook 
   AND y.guiduser = x.guiduser 
   AND y.bookmarkletter = x.bookmarkletter 
   AND y.attemptwrong > x.attemptwrong 
 WHERE y.idbook IS NULL;

...which scales poorly, but does at least afford the use of a view.

How to combine GROUP BY and MAX in mysql to get the full record with the max value of a field?

2 Answers2