0

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?

2

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
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
;
  • 3
    You should also join on the MIN(DateSubmission) otherwise you'll just get all records. – BlueGI Feb 28 '19 at 16:32
  • @BlueGI Oh yeah! You are absolutely right! – Kondybas Feb 28 '19 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.