I want to get the User with the highest points in SQL. I have this Table:
userId | points |
---|---|
1 | 1 |
2 | 0 |
3 | 4 |
1 | 1 |
3 | 2 |
2 | 5 |
Now, I want to get the userId where the points are highest? In this example it where user 3, but how I can do this automatically is SQL?
I think I have a solution for you please check the code below=>
CREATE TABLE USERS (userId int,points int);
INSERT INTO USERS VALUES(1,1);
INSERT INTO USERS VALUES(2,0);
INSERT INTO USERS VALUES(3,4);
INSERT INTO USERS VALUES(1,1);
INSERT INTO USERS VALUES(3,2);
INSERT INTO USERS VALUES(2,5);
INSERT INTO USERS VALUES(1,5);
THIS ss for MYSQL 8.0
WITH CTE AS
(SELECT USERID,
points,
RANK() OVER(ORDER BY points DESC) RNK
FROM USERS
)
SELECT * FROM CTE
WHERE RNK=1
NOTE: Check output in db-fiddle.