Billo Billo - 3 months ago 9
MySQL Question

PHP MySQL query - select all users but only display the earliest logon times for each user

I want to be able to bring back the earliest logon time per user, so only 1 record (the earliest record) displays for each user

I've tried various ways of

GROUP BY
but can't seem to get it quite right (if that is actually the correct way of doing).
username
is the unique value which can be used to GROUP BY

Here's the code I'm currently working with..

SELECT username, name, logon, added FROM data WHERE (date(added) LIKE '$date') AND (logon = (SELECT MIN(logon) FROM data))


I've also tried (below) but only get one result back, only displaying one user

WHERE (date(added) LIKE '$date') AND logon = (SELECT MIN(logon) FROM data)


The first image is what I'm currently getting, the second image is how I want my results to display, please see below




Let me know if you require anymore information, I've tried to put as much as possible

Thanks,
Tom




table

result





Answer

You are close. Your query needs a correlation clause:

SELECT d.*
FROM data d
WHERE date(d.added) = '$date' AND 
      d.logon = (SELECT MIN(d2.logon) FROM data d2 WHERE d2.name = d.name);

Note: The logic for added is confusing. First, you should not use like with dates. And, for that matter, you should not be inserting parameter values into the string, you should be using query parameters. And, actually, I don't see the need for that column; your question doesn't mention added.

Comments