mySQL > help for a Complex ORDER by query

Question

I am not an expert on SQL, so I am asking here some help about the following case :

I have 2 tables; 1 table "task" (ID,TITLE,CREATEDAT) and 1 table "activity" (ID,TASK_ID,TITLE,CREATEDAT) where CREATEDAT are a DATETIME column.

I would like to list the tasks ordering by last CREATEDAT column, in task and activity! I mean, have the most recent task (CREATEDAT AND which have the most recent activity, such an union of task & activity table...) but I have really no idea of how to process?

Thanks you for your help!

Will every task have an entry in the `activity` table? What's the precedence for the activity and task creation dates in the ordering? Please post the tables as [sample SQL code](http://sscce.org/). Note you can [format lines as code](http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) by indenting them four spaces. The "{}" button in the editor toolbar does this for you. Click the orange question mark in the editor toolbar for more information and tips on formatting. — outis, Jun 06 '11 at 00:35
Finally I have added a new column in my task table called "updatedat" and I set this column when I change the task title or description and also when I add a new activity to it ... — Thomas Decaux, Jun 16 '11 at 22:51

score 2 · Accepted Answer · edited Jun 06 '11 at 00:44

2

To order tasks by most recent activity or creation date if there is no activity on the task:

SELECT t.id, t.title 
  FROM task AS t
    LEFT JOIN activity AS a ON t.id=a.task_id
  GROUP BY t.id
  ORDER BY COALESCE(MAX(a.createdat), t.createdat) DESC

edited Jun 06 '11 at 00:44

Andriy M

76,112
17
94
154

answered Jun 06 '11 at 00:32

outis

75,655
22
151
221

eagle12 · Answer 2 · 2011-06-06T01:06:04.580

2

SELECT * FROM `task` T1 
    JOIN `activity` T2 ON T1.ID = T2.TASK_ID
    ORDER BY T1.CREATEDAT, T2.CREATDAT DESC

edited Jun 06 '11 at 01:06

answered Jun 06 '11 at 00:33

eagle12

1,658
11
14

Actually, it would have to be on the date/time fields DESCENDING order... The person wants most recent on top for BOTH classifications of entries.. – DRapp Jun 06 '11 at 00:50

score 1 · Answer 3 · edited May 23 '17 at 12:04

I think this is what you're looking for, if you want to show all tasks even if they don't have activities.

sekect task.id, task.title, activity.id, activity.title
from task
left join activity
order by task.createdat, activity.createdat desc

edit: I'll provide a short explanation that might help you understand a little more.

This statement basically tells the database to grab all the information in the specific columns "id" and "title" in the task table and the activity table - this is the sekect task.id, task.title, activity.id, activity.title clause.

Any query will always search at least one table, using the from keyword. In this case, we used from task, which would search the task table. However, for this example, you needed a union of two tables, requiring the use of the join keyword. Using join allows us to search more than one table, in multiple ways. We used a left join in this case; comment if you'd like a further explanation about this.

Finally, we simply ordered the data by the task creation date, and then the activity creation date, and specified descending order (oldest first, newest last).

Note that, in general, using select * isn't the best idea. It may satisfy your needs now, but if you're going to build more queries, you'll want to specify the columns you want to display, as in my example.

mySQL > help for a Complex ORDER by query

3 Answers3