MySQL Select Statement - GROUP BY / Unique

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;