Search code examples
sqloracle-databasetop-n

Show tasks which have taking the longest time sql

I need to show the tasks which have taken the longest to complete. So far I have: (But I’m aware that it isn’t working as when I round the tables my results show both 1 and 2 months when it should be only showing 2, as 2 months is the longest running time

SELECT t.task_name,  max(round(months_between  t.start_date,  t.end_date)), e.employee_id, e.task_id
FROM task t, employee e
WHERE t.task_id = e.task_id;
Solution

  • Order by difference descending, use FETCH FIRST to get the row with max difference. Add WITH TIES, to get ties.

    SELECT t.task_name, t.end_date - t.start_date AS diff, e.employee_id, e.task_id,
FROM task t
INNER JOIN employee e
    ON t.task_id = e.task_id
ORDER BY diff DESC
FETCH FIRST 1 ROW WITH TIES