Selecting unique players’ scores from two columns with bubble sorting

I have a very interesting challenge that I’m either looking too hard at (and missing a simple solution), or it is more complex than I can figure out heh

Essentially, I have a game that tracks scores. The table named scores has the columns:

id (pk, ai), email, name, duration, moves

A player (identified by email) can have multiple rows. To determine the winners, we apply bubble sorting on duration then moves. The challenge is this: How do we get a unique list (by email) of winners?

GROUP BY doesn’t work as expected because this is an aggregate. We need to keep parity between duration and moves.

Here’s an example of the schema and problem: http://sqlfiddle.com/#!9/0b1530/2 — notice how the same emails show up as expected.

This example uses GROUP BY, but it selects the wrong data: http://sqlfiddle.com/#!9/0b1530/3 — notice how [e-mail]’s score is incorrect. The correct score should be 30, 31 (duration & moves, respectively). [e-mail] should show 31, 35 (duration & moves, respectively)

And then lastly, selecting distinct on email column won’t work here either, example: http://sqlfiddle.com/#!9/0b1530/4

I’ve tried a few JOIN’s, but I cannot get those to work and also keep row parity either.

---

UPDATE #1

• The highest score is the amalgamation of first the lowest duration and then the lowest moves count
• The expected results from my example are:

email         name      duration   moves
[e-mail]   foo bar   20         36
[e-mail]   baz biz   30         31
[e-mail]   cow guy   31         35

---

UPDATE #2

Due to JSFiddle limitations, I’ve moved everything over to https://www.db-fiddle.com/f/vV7XMKkoFaynYEajLtYm9h/2

Please note that some of the queries in your answers do not work. I’ve used real data here (except changed emails/names) so that answers can test real data.

Here are the expected results from this sample data:

email           duration    moves
[e-mail]       32          22
[e-mail]       33          21
[e-mail]         38          23
[e-mail]         40          25
[e-mail]     41          22

Answer

One way to do this is using two nested IN expressions. The inner expression finds all combinations of email and duration that have the lowest duration for that email. The outer expression then finds the email, duration and moves values that are the lowest moves value for that duration (which is the lowest for that email).

SELECT *
FROM scores
WHERE (email, duration, moves) IN (
    SELECT email, duration, MIN(moves)
    FROM scores
    WHERE (email, duration) IN (
        SELECT email, MIN(duration)
        FROM scores
        GROUP BY email)
    GROUP BY email, duration)
ORDER BY duration, moves

Output:

id      email           name        duration    moves   submitted
219     [e-mail]     foo bar     20          36      2019-12-09T20:36:19Z
224     [e-mail]     baz biz     30          31      2019-12-09T20:36:19Z
233     [e-mail]     cow guy     31          35      2019-12-09T20:36:19Z

Demo on SQLFiddle

If you don’t care about the submitted time, you can remove the outer nested expression:

SELECT email, name, duration, MIN(moves) AS moves
FROM scores
WHERE (email, duration) IN (
    SELECT email, MIN(duration)
    FROM scores
    GROUP BY email)
GROUP BY email, name, duration
ORDER BY duration, moves

Output:

email           name        duration    moves
[e-mail]     foo bar     20          36
[e-mail]     baz biz     30          31
[e-mail]     cow guy     31          35

Demo on SQLFiddle

Demo with full data on dbfiddle