PHP MySQL查询 - 选择所有用户，但仅显示每个用户的最早登录时间

Question

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

---

---

Answer 1

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.