Can I order by a subquery?

DanielLiljeberg

First post here.
I've been learning myself php and sql and haven't really had any problems until latley. I'm working on a large comminity driven site from scratch.

I'm wondering if it's possible to order the results of a query by a subquery? I tried this:

"SELECT * FROM forum_posts WHERE is_topic = 1 AND category = " . $_REQUEST['id'] . " ORDER BY (SELECT time FROM forum_posts WHERE id = last_reply) ASC";

Where last_reply would be on of the fields returned by the initial query. Although it doesn't crash or anything it does not do what I'm after. It return the same as if I had no ORDER BY at all.

Installer

You're trying to order by the results of a query, which, after all, doesn't make sense. ORDER BY should be given just a column name to sort the main query results by.

DanielLiljeberg

Is there anyway to do what I want then? Beacuse to me it DO make sense. The idea is that I wish to sort all my TOPICS (wich is a forum_posts indicated by the fireld is_topic = 1) by when they last got a reply. And the last reply to a topic is stored as an index called last_reply (wich is the index of another forum_post that is the last reply). So the time filed in this "last_reply" (wich is when it was created) is what I want to order the topics by.

Hope it makes sense. 🙂

Installer

If I understand correctly, wouldn't this do it?

"SELECT * FROM forum_posts WHERE is_topic = 1 AND category = " . $_REQUEST['id'] . " ORDER BY last_reply ASC"

DanielLiljeberg

No, since last_reply is only an index to another forum_post entry. And there is no guarantee that the highest number is the last post since edited posts update the time but not their id.

Edit: Just had an idea. If I don't update the time when a user edits his post your query will work. But, then I noticed I had problems with my query when replying to posts. I create a new post in forum_posts and then I run "select last_insert_id()" and excpected to get the id of the post I just added. But I get something like "Resource 22" or somethinglike that. Any idea why?

Installer

Put a subquery or function in your column list that retrieves the indexed entry item, give it an alias, and order by the alias?

"SELECT *, (subquery/function) AS indexed_date FROM forum_posts WHERE is_topic = 1 AND category = " . $_REQUEST['id'] . " ORDER BY indexed_date ASC"

...or something like that. :queasy:

DanielLiljeberg

Yea.. That seemed to work. Or else it's just a fluke that it gives me the excpected result now... Thanx 😉