How can I use DISTINCT with a JOIN?

Question

I have those two tables:
users:

And playertimes:

(I know jumps and style should be int and not varchar() but that's not what I'm trying to solve)

I have a query, which is supposed to return the following results within the resultset:

name - retrieved using JOIN with auth
time
style

Here's my current query:
SELECT p.style, p.time, u.name FROM playertimes p JOIN users u ON p.auth = u.auth WHERE p.map = 'bhop_good' ORDER BY p.time ASC;

Here's the result I'm getting with the query above:

At this moment, the table can only contains two possible values of style which are 0 or 1. What I'm looking for, is to make style a DISTINCT, so in my case, I want to only get 2 results (one row per a value of style) for the query above, which should look like the following screenshot:

I'd like to receive help, thanks!

Check this answer: http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Reversal, Mar 26 '16 at 18:54
And consider using an unsigned integer to identify an user inside a db: it requires less space and it's much easier to handle with. — Reversal, Mar 26 '16 at 18:55

O. Jones · Accepted Answer · 2016-03-26T21:58:01.427

It looks like you want the style row for each distinct value of style having the smallest time value.

You get that like so. It takes two steps. The first determines the smallest time for each value

          SELECT style, MIN(time) time
            FROM playertimes
           GROUP BY style

The second step gets the actual playertimes row corresponding to that time.

    SELECT p.style, s.time, p.auth
      FROM playertimes p
      JOIN (
          SELECT style, MIN(time) time
            FROM playertimes
           GROUP BY style
           ) s ON p.style = s.style AND p.time = s.time

Finally, you can join that lot to your users table to turn your auth column into a name column.

    SELECT p.style, s.time, u.name
      FROM playertimes p
      JOIN (
          SELECT style, MIN(time) time
            FROM playertimes
           GROUP BY style
           ) s ON p.style = s.style AND p.time = s.time 
      JOIN users.u ON p.auth = u.auth

And, of course, once you have a working query you can add a WHERE clause to it, like this one.

    WHERE p.map = 'some constant'

How can I use DISTINCT with a JOIN?

1 Answers1