CODEWITHSUNDEEP
oracle-database
Jim Vercoelen
Jim Vercoelen

Reputation: 1077

SQL order by highest date values from other tables

I have this tables:

QUESTION

ID  |  Name      | date_time
1    question1      a date
2    question2      a date
3    question3      a date

ANSWER

ID  |  question_id | date_time
1      1             a date
2      1             a date
3      2             a date

COMMENT

ID  |  question_id | answer_id | date_time
1      NULL            1           a date
2      1               NULL        a date
3      2               NULL        a date

A question can have multiple answers AND comments

A answer belongs to a single question

And a comment belongs to a question OR answer! Not both on the same time.

I need to order the questions with the most recent 'activity', the latest date from a question, an answer or a reaction.

I already added up the tables and managed to order by dates

SELECT q.*, a.last_date_answer, r.last_date_reaction
FROM QUESTION q LEFT JOIN
      (SELECT question_id, MAX(date_time) AS last_date_answer
       FROM ANSWER a
       GROUP BY question_id) a
ON q.id = a.question_id LEFT JOIN
      (SELECT question_id, MAX(date_time) AS last_date_reaction
       FROM REACTION r
       GROUP BY question_id) r
ON q.id = r.question_id
ORDER BY date_time DESC,
     CASE WHEN last_date_answer IS NULL THEN 1 ELSE 0 END,
     CASE WHEN last_date_reaction IS NULL THEN 1 ELSE 0 END;

This gives the following dummy output:

date_time | - | last_date_answer | last_date_reaction
03-MAY-14   5   03-MAY-13          03-MAY-12
03-MAY-13   4   03-MAY-14          03-MAY-15
03-MAY-10   3   03-MAY-12          03-MAY-12

But I need the highest know date, which belongs by a single question (highest question, answer OR reaction date within a question). Can't figure it out.. I hope I was a bit clear!

Upvotes: 0

Views: 29

Answers (1)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Use GREATEST function in select statement to get the max date from these joined tables, then you can sort them by that column.
Your query should be looks like this :

SELECT q.*, a.last_date_answer, r.last_date_reaction,
GREATEST(date_time, 
    NVL(a.last_date_answer,TO_DATE('01/01/0001','DD/MM/YYYY')),
    NVL(r.last_date_reaction,TO_DATE('01/01/0001','DD/MM/YYYY'))
) X
FROM QUESTION q LEFT JOIN
      (SELECT question_id, MAX(date_time) AS last_date_answer
       FROM ANSWER a
       GROUP BY question_id) a
ON q.id = a.question_id LEFT JOIN
      (SELECT question_id, MAX(date_time) AS last_date_reaction
       FROM REACTION r
       GROUP BY question_id) r
ON q.id = r.question_id
ORDER BY X DESC

Upvotes: 1

Related Questions