select field information with min time value

I thought I was doing the right thing but I keep getting the wrong result. I am trying to simply find the id of the entry with the min time, but I am not getting that entry.

$qryuserscount1="SELECT id,min(entry_time) FROM scrusersonline WHERE topic_id='$开发者_StackOverflow中文版topic_id'";
$userscount1=mysql_query($qryuserscount1);
while ($row2 = mysql_fetch_assoc($userscount1)) {
    echo $onlineuser= $row2['id'];
}

That is my query, and it does not work. This however does work which does not make sense to me SELECT id FROM scrusersonline WHERE topic_id='$topic_id' ORDER by entry_time LIMIT 1, can anyone quickly point out what I am doing wrong?

---

SELECT id,min(entry_time) FROM scrusersonline WHERE topic_id='$topic_id'

will return id and entry_time for every row matching topic_id, since there is no group-by clause from which the min(entry_time) could be selected. The SELECT clause only defines what columns are to be returned, it doesn't restrict that selection by rows any - that's what the WHERE clause is for.

SELECT id FROM scrusersonline WHERE topic_id='$topic_id' ORDER BY entry_time LIMIT 1

will return id (SELECT id) for only the first (LIMIT 1) matching topic_id (WHERE topic_id='$topic_id') when ordered by entry_time (which is the minimum).

So, yes, SELECT id FROM scrusersonline WHERE topic_id='$topic_id' ORDER BY entry_time LIMIT 1 is what you're looking for.

---

I think that the min function has to be used with a group by clause. However, the second request you post selects the full id list where topic_id is what you want, ordered by entry_time (you could add an ASC or DESC which makes it clearest imo) and then, returns only the first.

What's wrong with it? :-)

---

SELECT  id, MIN(entry_time)
FROM    scrusersonline
WHERE   topic_id='$topic_id'

According to SQL standards, this query is invalid.

You cannot use id in the SELECT list unless you GROUP BY it, since it's not clear which id from the group should be returned.

MySQL, however, allows such queries which return any random id from the group (of from the whole recordset, if there is no GROUP BY clause).

This behavior is intended to simplify PRIMARY KEY joins with GROUP BY:

SELECT  mytable.*, COUNT(*)
FROM    mytable
JOIN    othertable
ON      othertable.col1 = mytable.id
GROUP BY
        mytable.id

In this case, all records from mytable are guaranteed to be the same within the group and it does not matter which one will be returned.

Your query will not return you the record holding the minimal value of entry_time. Instead, it will return you the minimal value of entry_time and a random id satisfying the topic_id = $topic_id condition which is not guaranteed to belong to the same record.