This content has been marked as final. Show 3 replies
Is there a reason that you have a DISTINCT in your query? It always makes me nervous to see that where it's not clearly necessary because it frequently means that a developer is missing a join condition and is using the DISTINCT to mask that fact.
On to the meat of your question, though, is there a potential for ties? If so, how do you want to handle that-- do you want two rows for that case, do you want to break the tie using some other column, do you want to pick an arbitrary row? If you want to pick an arbitrary row
If you want to do something else, just adjust the analytic function and use RANK or add a tie-breaker to the ORDER BY.
SELECT case_number, email, assignedDate_chart, last_date_entered, days_late, addl_days_late FROM ( SELECT a.*, row_number() over (partition by case_number order by date_entered) rn FROM (<<your query>>) a ) WHERE rn = 1
SELECT case_number, email, assigneddate_chart, date_entered, trunc(sysdate)-trunc(assigneddate_chart) days_late, trunc(sysdate)-trunc(date_entered) addl_days_late FROM ( SELECT c.case_number, u.email, c.assigneddate_chart, m.date_entered, dense_rank() over(partition by c.case_number order by m.date_entered DESC) rnk FROM chart c, chart_user_roles u, comments m WHERE c.case_status IN ('Open','Pending') AND c.case_number=m.case_number ) WHERE rnk = 1 /
Make your existing query into a sub-query.
Add the analytic RANK function to the SELECT clause, to number the rows in descending order, by date, with a different sequence of numebrs for each case_number.
In the new main query, pick only the rows where RANK assigned the number 1.
If you want help, post some sample data (CREATE TABLE and INSERT statements), that produce the results you already posted.
WITH got_rnum AS ( SELECT RANK () OVER ( PARTITION BY case_number ORDER BY last_date_entered DESC ) AS rnum , ... -- rest of your query ) SELECT * -- Or list all columns except rnum FROM got_rnum WHERE rnum = 1 ;