Page 2 of 2

Posted: Wed Oct 24, 2007 5:40 pm
by Benjamin
Didn't read the thread so I'm not sure if this is what your looking for or not..

Code: Select all

select
  t.id
from
  Thread t, Post p
where
  t.forum_id = '$id'
  and p.thread_id = t.id
order by
  p.updated desc, p.thread_id asc

Posted: Thu Oct 25, 2007 3:24 am
by Smasher
Unfortunately that makes no difference, still order by the thread id.

Also, it seems to me that isnt using the last post time and is relying on an id to order it.

Posted: Thu Oct 25, 2007 3:34 am
by Benjamin
I'm pretty sure the query I posted first is correct. Maybe you're just thinking that the output is wrong.

Code: Select all

mysql> SELECT t.title, t.forum_id, p.thread_id, p.updated FROM Thread t, Post p WHERE t.forum_id = '1' AND p.thread_id = t.id ORDER BY p.updated DESC , p.thread_id ASC;
+------------+----------+-----------+---------------------+
| title      | forum_id | thread_id | updated             |
+------------+----------+-----------+---------------------+
| Dorethy    |        1 |         2 | 2007-09-30 19:40:23 |
| Pete Burns |        1 |         1 | 2007-09-05 16:05:35 |
| Pete Burns |        1 |         1 | 2007-09-04 22:20:38 |
+------------+----------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql>
This seems correct to me. The primary order is most recent posts first (descending), then by thread_id in ascending order. Is that not what you want?

Posted: Thu Oct 25, 2007 4:09 am
by Smasher
Nope, sorry - maybe I didn't explain properly.

The order should be by the last post - even the thread result should be ordered by the last post.

So the last thread posted in is at the top of the list.

Posted: Thu Oct 25, 2007 4:13 am
by Benjamin
I guess I don't understand what your trying to accomplish. Sorry.

Posted: Thu Oct 25, 2007 5:07 am
by Smasher
A list of threads ordered by the last post.

Posted: Thu Oct 25, 2007 5:54 am
by Benjamin
I have already demonstrated a working query which orders the results by the p.updated field in descending order. Based on the sample data you provided, the proof is a few posts above this.

Posted: Thu Oct 25, 2007 7:41 am
by Weirdan

Code: Select all

select thread.id, thread.title, max(post.updated)
from 
 threads thread
inner join
 posts post
on post.thread_id = thread.thread_id
group by thread.thread_id
order by max(post.updated) desc

Posted: Thu Oct 25, 2007 8:40 am
by Benjamin
Tweaked the table names..

Code: Select all

mysql> SELECT post.id, thread.title, max(post.updated) FROM  Thread thread INNER JOIN  Post post ON thread.id = post.thread_id where thread.forum_id = 1 GROUP BY post.thread_id ORDER BY max(post.updated) DESC;
+----+------------+---------------------+
| id | title      | max(post.updated)   |
+----+------------+---------------------+
|  6 | Dorethy    | 2007-09-30 19:40:23 |
|  1 | Pete Burns | 2007-09-05 16:05:35 |
+----+------------+---------------------+
2 rows in set (0.00 sec)

mysql>

Posted: Thu Oct 25, 2007 10:18 am
by Smasher
I get

Invalid use of group function

:(

Posted: Thu Oct 25, 2007 11:01 am
by Weirdan
What version of mysql you are running? And don't you run it in ANSI mode by any chance?

btw, saying "It doesn't work", or even posting the results alone without posting what you did to get them not helping us help you. Do it like astions did: fire up your mysql command line client, type the query then copy both the query and result and post here.

Posted: Thu Oct 25, 2007 11:08 am
by Smasher
MySQL said: Documentation
#1111 - Invalid use of group function

Posted: Mon Nov 05, 2007 5:08 pm
by Smasher
PHP version 5.2.3 / MySQL version 4.1.22-standard

I don't know what your refering to with ANSI. The error I recieve is "Invalid use of group function" via a script/phpmyadmin sql query.

The exact query is;

Code: Select all

SELECT p.`id`, t.`title`, MAX(p.`updated`) 
FROM  `Thread` t 
INNER JOIN `Post` p ON (t.`id` = p.`thread_id`)
WHERE t.`forum_id` = $id 
GROUP BY p.`thread_id`  
ORDER BY MAX(p.`updated`) DESC 
EDIT:

If I remove the MAX on the orderby, then the query doesn't error, however the order is once again done by the thread id and not by p.updated :s

Code: Select all

SELECT t.`id` FROM `Thread` t 
								JOIN `Post` p ON (t.`id` = p.`thread_id`)
								WHERE t.`forum_id` = $id 
								GROUP BY p.`thread_id`  
								ORDER BY p.`updated` DESC