2 Replies Latest reply: Jul 27, 2011 9:09 PM by Frank Kulash RSS

    Select max date when both dates are identical

    878500
      There has to be a way to select the max date from a table when both dates are identical. Here is what I tried but it is giving me a single-row query error:

      select a.sfrstca_activity_date
      from sfrstca a
      where a.sfrstca_term_code = '201201'
      and to_char(a.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss') =
      (select to_char(max(b.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss'))
      from sfrstca b
      where a.sfrstca_pidm = b.sfrstca_pidm)
      and a.sfrstca_pidm = pidm)

      Any ideas how I can make this work?

      Thanks!
        • 1. Re: Select max date when both dates are identical
          Brian Bontrager
          I presume from the extra parenthesis at the end that this code is pulled out of some other subquery? If so, add a DISTINCT.

          The syntax error with your combination of MAX() and [unnecessary] TO_CHAR() in the subquery may also be part of the issue.

          Try this (untested):
          select DISTINCT a.sfrstca_activity_date
          from sfrstca a
          where a.sfrstca_term_code = '201201'
          and a.sfrstca_activity_date =
          (select max(b.sfrstca_activity_date)
          from sfrstca b
          where a.sfrstca_pidm = b.sfrstca_pidm)
          and a.sfrstca_pidm = pidm
          • 2. Re: Select max date when both dates are identical
            Frank Kulash
            Hi,

            Welcome to the forum!
            875497 wrote:
            There has to be a way to select the max date from a table when both dates are identical. Here is what I tried but it is giving me a single-row query error:

            select a.sfrstca_activity_date
            from sfrstca a
            where a.sfrstca_term_code = '201201'
            and to_char(a.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss') =
            (select to_char(max(b.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss'))
            from sfrstca b
            where a.sfrstca_pidm = b.sfrstca_pidm)
            and a.sfrstca_pidm = pidm)

            Any ideas how I can make this work?
            You gave the solution yourself!
            The way to select the max date is "SELECT MAX ( +date+ )":
            select MAX (a.sfrstca_activity_date)  AS max_date
            from sfrstca a
            where a.sfrstca_term_code = '201201'
            and to_char(a.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss') =
                            (select to_char(max(b.sfrstca_activity_date,'dd-mon-yyyy hh:mi:ss'))
                            from sfrstca b
                            where a.sfrstca_pidm = b.sfrstca_pidm)
            and a.sfrstca_pidm = pidm)
            When you use an aggreate function (like MAX) without a GROUP BY clause, the query will always return 1 row, regardless of how many rows meet the WHERE clause conditions.

            This question doesn't really have anything to do with the SQL*Plus or iSQL*Plus front ends. Isn't it more of a SQL language question? You'll get better replies to SQL questions if you post them in the "SQL and PL/SQL" forum:
            PL/SQL

            If you're not sure whether something is a SQL question or not, post it in the "SQL and PL/SQL" forum; more people read it.