MySQL Select Statement - GROUP BY/Unique

Question

Hello, I'm currently using PHP/MySQL to select some data from my database. My current SELECT statement is:

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).

For example, there are 4 columns all with the same 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).

Any ideas on how to achieve this with my SELECT statement? Thanks.

Answer 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.

Answer 2

SELECT thread_id, max(unique_id)
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id;

Answer 3

SELECT DISTINCT thread_id, unique_id 
FROM tblpm
WHERE receiver_id = '$usrID'
GROUP BY thread_id
ORDER BY unique_id DESC;