Mitch Mitch - 2 months ago 8
MySQL Question

Select Distinct from new tol old in same table

I have one table named:
thread_comment

Now the table is getting filled as followed:

thread_comment_id thread_id user_id thread_comment thread_comment_time
1 2 1 This is a comment 2016-09-14 15:30:28
2 4 1 This is a comment 2016-09-14 15:32:28
3 2 1 This is a comment 2016-09-14 15:33:28
4 5 1 This is a comment 2016-09-14 15:34:28
5 7 1 This is a comment 2016-09-14 15:35:28
6 2 1 This is a comment 2016-09-14 15:37:28
7 2 1 This is a comment 2016-09-14 15:40:28


I want to show the newest threads to my page. for example:

as number one i want thread_comment_id 7
as number two i want thread_comment_id 5

I skipped 6 because i don't want any duplicates in the list.

In order to do so i did the folowing:

$sql = "SELECT DISTINCT thread_id FROM thread_comment ORDER BY thread_comment_time DESC"


This is kind of working (Not showing any duplicates). However the order does not make any sense...

For example:

It goes like 5 - 6 -4 - 7 etc...

Answer

The column used in the ORDER BY isn't specified in the DISTINCT. You need to use an aggregate function and GROUP BY to make the DISTINCT work.

SELECT DISTINCT thread_id, max(thread_comment_id) FROM thread_comment GROUP BY thread_id ORDER BY max(thread_comment_id) DESC, thread_id

EDIT: added aggregate func max() Also thread_id is not mandatory in the ORDER BY