This discussion is archived
2 Replies Latest reply: Jul 27, 2011 7:09 PM by Frank Kulash RSS

Select max date when both dates are identical

878500 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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:
    SQL and 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.

Legend

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