How to get the best time from submited values?

Question

I have a MySQL table, and i'm trying to figure how can i get the first result for each user.

For example:

Email               Time    DateSubmission
usera@email.com   | 0:14  | 28/2/2019 00:10
usera@email.com   | 0:10  | 28/2/2019 00:08
usera@email.com   | 0:10  | 29/2/2019 00:10
userb@email.com   | 0:10  | 28/2/2019 00:04
userb@email.com   | 0:11  | 28/2/2019 00:17
userc@email.com   | 0:11  | 28/2/2019 00:18

I've tried to use distinct, order by's but can't find how to get results like:

Email               Time    DateSubmission
userb@email.com   | 0:10  | 28/2/2019 00:04
usera@email.com   | 0:10  | 28/2/2019 00:08
userc@email.com   | 0:11  | 28/2/2019 00:18

Can anyone help?

Answer 1

You should be able to use CTE (common table expression) along with the ROW_NUMBER() function to select the top 1 for each email.

See here for a short primer on ROW_NUMBER().
And here for CTE.

Something like:

WITH CTE AS (
SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email, datesubmission) R, *
FROM MyTable
)
SELECT * FROM CTE
WHERE R = 1
ORDER BY datesubmission, email

Answer 2

This is another case of well-known MAX BY GROUP that can be solved by nested select:

SELECT * 
  FROM table AS a
  JOIN (SELECT Email
             , MIN(DateSubmission) AS minds
          FROM table 
         GROUP BY Email
       ) AS b  ON b.Email = a.Email
              AND b.minds = a.DateSubmission
;