ORDER BY in MYSQL with condition using a return of a function

Question

i was looking for a solution; the query is:

            SELECT MAX(forum_commenti.Data) AS MData, forum_post.id AS id, forum_post.Nome AS Nome, forum_post.Messaggio AS Messaggio, forum_post.Sezione AS Sezione, forum_post.Data AS Data, forum_post.Utente AS Utente, forum_post.Chiuso AS Chiuso, forum_post.Importante AS Importante
            FROM forum_post LEFT OUTER JOIN forum_commenti ON forum_post.id = forum_commenti.Post
            WHERE forum_post.Importante = 0
            AND forum_post.Sezione = '".$_GET['id']."'
            GROUP BY id, Nome, Messaggio, Sezione, Data, Utente, Chiuso, Importante
            ORDER BY MData IS NOT NULL DESC, Data DESC
            LIMIT $start, $per_page

This is a query for a forum; i'm trying to display the order of posts for Data. My want would be:

If a post haven't replys, order that post using his own date, else use the date of the last comment for that post. (other informations are useless)

I looked for old questions solved about something like this but it gave me problem when i try to do like:

ORDER BY MData IS NOT NULL DESC, Data ASC

It says "Reference 'MData' not supported (reference to group function)".

I'm using that query for a php function.

The tables involved in the query are:

forum_post : Contains all the posts of the forum
forum_commenti : Contains all the replys of all posts, using 'Post' as a foreign key of forum_post

The WHERE condition is a useless point for the query.

I'll show you the example:

POST 1 | Data of the last reply is 19/12/2014 10:00:00 , Data of post is 19/12/2014 09:00:00 TAKE Data of the last comment

POST 2 | Data of the last reply is NULL (hasen't got replys), Data of post is 19/12/2014 08:00:00 TAKE Data of the post

....... ....... etc

Now that i have something like that:

POST 1 | 19/12/2014 10:00:00

POST 2 | 19/12/2014 08:00:00

ORDER BY Data

Thanks you all for helping.

can't you use a command similar to Oracle Decode? If present take the last comment date, otherwise take the post date, giving the same name you can order by it maybe — Marco Mura, Dec 19 '14 at 08:04
I'm looking for something like that. I tryed using 'ORDER BY WHEN', but the warning is still there; i can't use MData, that's a return of an aggregate function used in SELECT (MAX). — Mauri, Dec 19 '14 at 08:06
it's not used like that. Do a subquery with if (http://stackoverflow.com/questions/4706100/mysql-equivalent-of-decode-function-in-oracle-and-intersystem-cache-database) ,but load the data with the same name, not with aggregate, Like select data from myTable order by data desc limit 1, if null take the post, etc — Marco Mura, Dec 19 '14 at 08:08
Okay i got it and i tryed, i've put in a FROM all that query, from SELECT to GROUP BY; after that, in the new SELECT i've put the same fields as first, but using the new alias, and it dosen't give me the same error. In the ORDER BY, of the external query, i've inserted the condition i've used in the post (iS NOT NULL..), it works fine, but i haven't got the right result. I'm gonna edit the main post and show the resolut that i wish for that query. — Mauri, Dec 19 '14 at 08:21
You're actual output is the one after Now that i have something like that ? If yes you now need to order those results only? If yes have you tried ordering it by ORDER BY Data ASC or DESC ? It change something? — Marco Mura, Dec 19 '14 at 08:32
Yes, if i use ORDER BY Data ASC/DESC it shows all the post without replys first, after the posts with replys. But this is not the order I need; what I need is in the example. — Mauri, Dec 19 '14 at 08:56

score 1 · Answer 1 · answered Jun 03 '15 at 19:48

It works when using a subquery that first selects the posts together with the date of the latest comment. The result can then be sorted according to an expression on these two columns. I used IFNULL(last_post_date, own_date) to pick the correct date, and also make this available in an effective_date column for didactical purposes:

SELECT *, IFNULL(last_post_date, own_date) AS effective_date 
FROM (
  SELECT 
    forum_post.post_id, 
    forum_post.date AS own_date, 
    MAX(forum_comment.date) AS last_post_date 
  FROM forum_post 
  LEFT OUTER JOIN forum_comment 
  ON (forum_post.post_id = forum_comment.post_id) 
  GROUP BY post_id
) posts 
ORDER BY effective_date DESC;

Note that I have used a slightly different table setup in my answer because the original table definitions were not available, but it will also work with any other table setup. Here are my example tables:

CREATE TABLE `forum_post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`post_id`)
);
CREATE TABLE `forum_comment` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`comment_id`)
);

I used the following example data for testing:

forum_post.

+---------+---------------------+
| post_id | date                |
+---------+---------------------+
|       1 | 2014-12-01 00:00:00 |
|       2 | 2014-12-02 00:00:00 |
|       3 | 2014-12-02 00:00:00 |
|       4 | 2014-12-03 00:00:00 |
|       5 | 2014-12-03 00:00:00 |
|       6 | 2014-12-04 00:00:00 |
|       7 | 2014-12-06 00:00:00 |
|       8 | 2014-12-09 00:00:00 |
+---------+---------------------+

forum_comment:

+------------+---------+---------------------+
| comment_id | post_id | date                |
+------------+---------+---------------------+
|          1 |       1 | 2014-12-01 00:00:00 |
|          2 |       1 | 2014-12-02 00:00:00 |
|          3 |       1 | 2014-12-03 00:00:00 |
|          4 |       2 | 2014-12-23 00:00:00 |
|          5 |       3 | 2014-12-09 00:00:00 |
|          6 |       3 | 2014-12-15 00:00:00 |
|          7 |       5 | 2014-12-15 00:00:00 |
|          8 |       7 | 2014-12-09 00:00:00 |
|          9 |       7 | 2014-12-11 00:00:00 |
+------------+---------+---------------------+

With that data, the query returns the following result:

+---------+---------------------+---------------------+---------------------+
| post_id | own_date            | last_post_date      | effective_date      |
+---------+---------------------+---------------------+---------------------+
|       2 | 2014-12-02 00:00:00 | 2014-12-23 00:00:00 | 2014-12-23 00:00:00 |
|       3 | 2014-12-02 00:00:00 | 2014-12-15 00:00:00 | 2014-12-15 00:00:00 |
|       5 | 2014-12-03 00:00:00 | 2014-12-15 00:00:00 | 2014-12-15 00:00:00 |
|       7 | 2014-12-06 00:00:00 | 2014-12-11 00:00:00 | 2014-12-11 00:00:00 |
|       8 | 2014-12-09 00:00:00 | NULL                | 2014-12-09 00:00:00 |
|       6 | 2014-12-04 00:00:00 | NULL                | 2014-12-04 00:00:00 |
|       4 | 2014-12-03 00:00:00 | NULL                | 2014-12-03 00:00:00 |
|       1 | 2014-12-01 00:00:00 | 2014-12-03 00:00:00 | 2014-12-03 00:00:00 |
+---------+---------------------+---------------------+---------------------+

ORDER BY in MYSQL with condition using a return of a function

1 Answers1