MYSQL Return all rows with additional column for max value for the set without grouping

I know it is weird to try and get the MAX value without a GROUP BY, but maybe there is a way to do what I need.

Lets say I have the following table…

What I need to be able to do is return all rows where the score is > 30, but I also need to know what the max value is in that result set. My actual WHERE clause is a bit complicated, so I need to avoid duplicating the WHERE part, or querying the data twice to get the answer.

My ideal scenario/pseudo query…

SELECT UserID, UserName, UserScore, GROUP_MAX(UserScore) AS ScoreMax FROM Users
WHERE UserScore > 30
ORDER BY UserScore;

Result set would look like this…

Then at any point I can reference ScoreMax at the row level.

Tried subqueries and UNION but both proved to be too slow for our large dataset.

You need to sign in to view this answers