This discussion is archived
3 Replies Latest reply: Nov 17, 2009 8:11 AM by Frank Kulash RSS

SQL query using MAX function

658699 Newbie
Currently Being Moderated
I am trying to only display the records where the 'date_entered' is the most recent date per case number.

SELECT distinct c.case_number, u.email,c.assigneddate_chart,
--m.date_entered,
max(m.date_entered)as last_date_entered,
trunc(sysdate)-trunc(c.assigneddate_chart)days_late,
trunc(sysdate)-trunc(m.date_entered)addl_days_late
from chart c, chart_user_roles u,comments m
where 
(c.case_status IN ('Open','Pending')) and 
c.case_number=m.case_number
group by c.case_number, 
u.email,c.assigneddate_chart,m.date_entered
Right now, this is the output im am getting.

Output:
CASE_NUMBER------EMAIL---------ASSIGNEDDATE_CHART---LAST_DATE_ENTERED---DAYS_LATE--ADDL_DAYS_LATE
2009100103----xxx@cox.net--------------17-OCT-09---------------------06-NOV-09-----------------31--------------------11
2009100103----xxx@cox.net--------------17-OCT-09---------------------10-NOV-09-----------------31---------------------7
2009100103----xxx@cox.net--------------17-OCT-09---------------------06-NOV-09-----------------31--------------------11
2009100103----xxx@cox.net--------------17-OCT-09---------------------06-NOV-09-----------------31--------------------11
2009100103----xxx@cox.net--------------17-OCT-09---------------------06-NOV-09-----------------31--------------------11
2009022501----xxx@cox.net--------------14-OCT-09---------------------03-NOV-09-----------------34--------------------14


I am wanting to achieve this output:

Therefore, based on the data below, the only records that i am wanting to display are:
2009100103----xxx@cox.net--------------17-OCT-09---------------------10-NOV-09-----------------31---------------------7
2009022501----xxx@cox.net--------------14-OCT-09---------------------03-NOV-09-----------------34--------------------14


Thanks
Deanna
  • 1. Re: SQL query using MAX function
    Justin Cave Oracle ACE
    Currently Being Moderated
    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
    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
    If you want to do something else, just adjust the analytic function and use RANK or add a tie-breaker to the ORDER BY.

    Justin
  • 2. Re: SQL query using MAX function
    Solomon Yakobson Guru
    Currently Being Moderated
    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
    /
    SY.
  • 3. Re: SQL query using MAX function
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Deanna,

    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.

    That is:
    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
    ;
    If you want help, post some sample data (CREATE TABLE and INSERT statements), that produce the results you already posted.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points