mysqlsqlmariadbhighest

Get user with highest points


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?


Solution

  • 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.