3 Replies Latest reply: Nov 17, 2009 10:11 AM by Frank Kulash RSS

    SQL query using MAX function

    658699
      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
          JustinCave
          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
            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
              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.