How can I filter only the highest score of each user in MySQL?

I’m not familiar with back-end stuff too much, I have managed to join the table back together after a struggle of the time.

I have manage to join and get all the data join together, it display like this in my POSTMAN Json when I pull it to the front-end.

[
    {
        "rank_ID": 1,
        "history_ID": 427,
        "score": 100,
        "username": "karame123",
        "user_ID": 26,
        "quiz_ID": 6
    },
    {
        "rank_ID": 4,
        "history_ID": 463,
        "score": 60,
        "username": "testing_demo",
        "user_ID": 22,
        "quiz_ID": 6
    },
    {
        "rank_ID": 11,
        "history_ID": 468,
        "score": 0,
        "username": "testing_demo",
        "user_ID": 22,
        "quiz_ID": 1
    },
    {
        "rank_ID": 13,
        "history_ID": 469,
        "score": 50,
        "username": "testing_demo",
        "user_ID": 22,
        "quiz_ID": 2
    },
    {
        "rank_ID": 15,
        "history_ID": 470,
        "score": 100,
        "username": "testing_demo",
        "user_ID": 22,
        "quiz_ID": 1
    }
]

Here’s my query in MySQL to achieve it:

SELECT ranklist.*, histories.history_ID, users.username, users.user_ID, quizs.quiz_ID 
FROM ranklist 
    LEFT JOIN histories ON (histories.history_ID = ranklist.history_ID)
    LEFT JOIN users ON (users.user_ID = histories.user_ID) 
    LEFT JOIN quizs ON (quizs.quiz_ID = histories.quiz_ID)

I haven’t implemented the WHERE quiz_ID = ? to insert the quiz_ID I want in the front-end, i’m still trying to figure out how to filter it as I want with POSTMAN

I want to filter only the highest score of each user_ID in a specific quiz_ID.

For example, at quiz_ID = 6, there might be 3 players do the quiz, and they can do it multiple times, so I only want to filter out their best performance, which is their highest score

Here’s the rank list table that I’ve made:

How can I filter only the highest score of each user in MySQL?

Inside the history_ID column is related to the histories table, which is related to usernames and the quiz_ID info, but I have just joined it all together like the Json above

How can I filter only the highest score of each user in MySQL?

To this step, my brains start to freeze and I can’t think of any situation nor solution, even though I do some research and search, for example (GROUP BY, something like Subquery ), I still can’t figure it out in my scenario, I need some help, and thanks for reading. 🙂

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If I understood correctly what you are looking for is something like

SELECT user_ID, quiz_ID ,MAX(score) from yourTable WHERE quiz_ID = id GROUP BY user_ID

It has been a while since last time I practiced SQL but it should be something like that.

Method 2

Use ORDER BY Score DESC to get best score. I think that will solve your question.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x