SQL - SELECT DISTINCT question ...

[fractalbit]

Hi, this not really a php question but an sql one. Sorry for being a little off topic.

I want to make a query that selects the most recent theads at the board. The problem is i want to select only unique threads, not dublicates. I tried that :

Code Select Expand

$query = "SELECT DISTINCT id_topic, subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";
and that :

Code Select Expand

$query = "SELECT DISTINCT(id_topic), subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";
but neither worked

Note that i have tried to run the above queries directly to the mySQL database through phpmyadmin but to no avail. I always get duplicate threads  . So it's not a php problem with my code but probably an SQL one.

So as it currently stands i run this query :

Code Select Expand

$query = "SELECT id_topic, subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";

and then i store the topic_ids in a table discarding ids that have already been stored. This makes it difficult to impossible to implement pages (like 1 | 2 | 3 ... | 9 | 10) in the app. So any help to do this directly with mySQL will be greatly apprecieted.

[[Unknown]]

Add "GROUP BY t.ID_TOPIC" to the end of the query.

-[Unknown]

[fractalbit]

It doesnt work, it gives a syntax error. (with or without t)

[[Unknown]]

"SELECT ID_TOPIC, subject, ID_MSG, posterTime
FROM yabbse_messages
GROUP BY ID_TOPIC
ORDER BY posterTime
DESC LIMIT 0, $max"

-[Unknown]

[Haase]

Man, that's simple...  I would have done it the much more complicated way. 

But how would you know that when you group by ID_TOPIC that it's it's always going to pull the first message from the thread?

Code Select Expand

"SELECT t.ID_TOPIC, m.subject,  m.ID_MSG, m.posterTime
FROM yabbse_messages AS m, yabbse_topics AS t
WHERE m.ID_TOPIC = t.ID_TOPIC
AND m.ID_MSG = t.ID_FIRST_MSG
ORDER BY posterTime
DESC LIMIT 0, $max"

Also, although my query took much longer to type, it runs faster.  Going straight through phpMyAdmin, my query ran average of 0.0026 seconds...  yours averaged at 0.0099.  Must be the GROUP BY function, though I honestly thought that having to do a table join would take much longer.

[fractalbit]

Thanks for the replies. I tried both queries. They both run fine without syntax error but none of them returns the results i wanted. They return unique threads but they are not ordered by postertime or they seem to jump some threads. I dont know why this is happening.

[Haase]

We have a recent topics block in PfaBB/Enigma, but the query is a lot more complex:

Code Select Expand

         $request = mysql_query("
          SELECT m.smiliesEnabled,m.posterTime,m.ID_MEMBER,m.ID_MSG,m.subject,m.body,m.ID_TOPIC,t.ID_BOARD,b.name as bname,
            c.name as cname,m.posterName,t.numReplies
          FROM {$db_prefix}messages as m,{$db_prefix}topics as t,{$db_prefix}boards as b,{$db_prefix}categories as c
          WHERE (m.ID_TOPIC = t.ID_TOPIC AND t.ID_BOARD = b.ID_BOARD AND b.ID_CAT=c.ID_CAT AND (FIND_IN_SET('$settings[7]',
            c.memberGroups)!=0 OR c.memberGroups='' OR '$settings[7]'='Administrator' OR '$settings[7]'='Global Moderator'))
          ORDER BY m.posterTime DESC");