MySQL: select only some grouped rows with values ​​related to the highest ID

Question

I have the following database (small example):

ID | username | action | points | example 1 | matt | login | 3 | 135 2 | john | comment | 6 | 156 3 | john | login | 6 | 189 4 | peter | login | 8 | 125 5 | peter | login | 8 | 165 

I wanted to select and group user names that have the same login action and the number of points above 5 (for all actions). All actions must be login . Thus, the results will be without john , because there is a comment action associated with this username.

Related question: MySQL: select only some grouped rows

Possible Solution:

 SELECT username, COUNT(*) AS cnt, SUM(points) AS points FROM tableX AS t GROUP BY username HAVING MIN(action) = 'login' AND MAX(action) = 'login' AND SUM(points) > 5 ; 

And the results:

 username | COUNT | points(SUM) peter | 2 | 16 

But then I would like to add to the results all the other values ​​from the row with the highest ID. I tried the subquery but did not find the right solution.

Expected results:

 ID | username | COUNT | points(SUM) | points | example 5 | peter | 2 | 16 | 8 | 165 

Do you have any ideas? Thank you very much!

SQL Fiddle

Answer 1

Try the following:

 SELECT DISTINCT MAX(ID), username, action, SUM(points) as pointsSum, MAX(points) as pointsMax, MAX(example) as example FROM tableX WHERE action='login' GROUP BY username