General Community > Scripting Help

SQL - SELECT DISTINCT question ...

(1/2) > >>

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: ---$query = "SELECT DISTINCT id_topic, subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";
--- End code ---
and that :


--- Code: ---$query = "SELECT DISTINCT(id_topic), subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";
--- End code ---
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: ---$query = "SELECT id_topic, subject, id_msg, posterTime FROM yabbse_messages ORDER BY posterTime DESC LIMIT 0, $max";
--- End code ---

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 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"
--- End code ---

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.

Navigation

[0] Message Index

[#] Next page