WITH T1 AS (
SELECT AVG(POINT) AS AVE
FROM TEST_RESULTS
WHERE TEST_ID='100'
),T2 AS (
SELECT AVE,AVG((POINT-AVE)*(POINT-AVE)) AS AVE_PO_AVE
FROM TEST_RESULTS AS TEST CROSS JOIN T1
WHERE TEST_ID='100'
)
SELECT USER_ID USER,POINT PT,CASE WHEN ROUND((POINT-AVE)*10/SQRT(AVE_PO_AVE),1)=0 THEN 50 ELSE ROUND((POINT-AVE)*10/SQRT(AVE_PO_AVE),1)+50 END AS DEV_VAL
FROM TEST_RESULTS AS TEST CROSS JOIN T2
WHERE TEST_ID='100'
ORDER BY DEV_VAL DESC,USER_ID