Ordering by a different table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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?
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I guess I don't understand what your trying to accomplish. Sorry.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

A list of threads ordered by the last post.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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>
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

I get

Invalid use of group function

:(
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post by Smasher »

MySQL said: Documentation
#1111 - Invalid use of group function
Smasher
Forum Commoner
Posts: 38
Joined: Fri Apr 20, 2007 5:22 am

Post 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 
Post Reply