CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Bunny
Bunny

Reputation: 646

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:

enter image description here

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

enter image description here

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. :)

Upvotes: 1

Views: 233

Answers (2)

Oguzhan
Oguzhan

Reputation: 255

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.

Upvotes: 2

nighthawk
nighthawk

Reputation: 832

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

Upvotes: -1

Related Questions