MYSQL Get Difference between two values

Question

I am wanting to calculate the difference between the max(value) returned from a database against a row value returned.

Example Data

1. 400
2. 300
3. 200
4. 100

max(value return) = 400

Returned result should be:

1. 0 (no difference between the max value and row value)
2. 100
3. 200
4. 300

etc

Any ideas how this could achieve?

This is current mySQL statement I am using:

SELECT userName, totalPoints , (totalPoints) AS gap
FROM userTable
WHERE value='1'  
ORDER BY totalPoints DESC

s

Answer 1

Try this below soln.

SELECT A.MaxtotalPoints - A.totalPoints AS GAP
FROM
(
SELECT userName, totalPoints , MAX(totalPoints) MaxtotalPoints
FROM userTable
WHERE value='1'  
) A
ORDER BY GAP;