I'm currently using PHP/MySQL to select some data from my database. My current SELECT statement is:
我目前正在使用PHP / MySQL从我的数据库中选择一些数据。我当前的SELECT语句是:
mysql_query("SELECT * FROM tblpm WHERE receiver_id ='$usrID' GROUP BY
thread_id ORDER BY unique_id DESC") or die(mysql_error());
There are some columns that have the same "thread-id", thus, I am using the GROUP BY, so that only one "thread-id" column is selected. Everything works fine with the grouping, however, it is selecting the first 'thread-id' column, and not the most recent (which is what I want).
有些列具有相同的“thread-id”,因此,我使用GROUP BY,因此只选择了一个“thread-id”列。一切都适用于分组,但是,它选择的是第一个'thread-id'列,而不是最新的(这是我想要的)。
For example, there are 4 columns all with the same thread_id:
例如,有4列都具有相同的thread_id:
thread_id unique_id
222 1
222 2
222 3
222 4
When I do the GROUP BY, I would like it to retrieve the one with unique id 4 (the most recently created column), not unique id 1 (the oldest column created).
当我执行GROUP BY时,我希望它检索具有唯一ID 4(最近创建的列)的那个,而不是唯一ID 1(创建的最旧列)。
Any ideas on how to achieve this with my SELECT statement? Thanks.
有关如何使用SELECT语句实现此目的的任何想法?谢谢。
3 个解决方案
#1
SELECT thread_id, MAX(unique_id)
FROM tblpm
GROUP by thread_id
so:
mysql_query(<<<END
SELECT thread_id, MAX(unique_id)
FROM tblpm
WHERE receiver_id ='$usrID'
GROUP by thread_id
END
) or die(mysql_error());
and of course make sure you escape $usrID if it comes from the browser.
当然,如果它来自浏览器,请确保您转义$ usrID。
#2
SELECT thread_id, max(unique_id)
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id;
#3
SELECT DISTINCT thread_id, unique_id
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id
ORDER BY unique_id DESC;
#1
SELECT thread_id, MAX(unique_id)
FROM tblpm
GROUP by thread_id
so:
mysql_query(<<<END
SELECT thread_id, MAX(unique_id)
FROM tblpm
WHERE receiver_id ='$usrID'
GROUP by thread_id
END
) or die(mysql_error());
and of course make sure you escape $usrID if it comes from the browser.
当然,如果它来自浏览器,请确保您转义$ usrID。
#2
SELECT thread_id, max(unique_id)
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id;
#3
SELECT DISTINCT thread_id, unique_id
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id
ORDER BY unique_id DESC;