Skip to main content
J
jenn1
Inspiring
June 23, 2008
Question

date prior to MAX(date)

  • June 23, 2008
  • 3 replies
  • 679 views
I need a query to search by the max(date) on a table. If there's nothing returned for the MAX(date), then query the on the day before the MAX(date) until something is returned. Can this be done with SQL?

    This topic has been closed for replies.

    3 replies

    D
    dongzky
    Inspiring
    June 24, 2008
    First of all, what db are you using?

    J
    jenn1Author
    Inspiring
    June 24, 2008
    We're using Sybase.
    P
    paross1
    Participating Frequently
    June 23, 2008
    What are you using for your initial MAX(date)?

    Phil
    J
    jenn1Author
    Inspiring
    June 23, 2008
    I'm using a publication date
    P
    paross1
    Participating Frequently
    June 23, 2008
    Just off the top of my head, but maybe something like this to give you the record with the most recent date that is less than or equal to the publication date:

    SELECT t.pri_key, t.your_other_fields
    FROM your_table t
    WHERE t.your_date = (SELECT MAX(t1.your_date)
    FROM your_table t1
    WHERE t1.pri_key = t.pri_key
    t1.your_date <= #publication date#)

    Phil
    S
    Stressed_Simon
    Inspiring
    June 23, 2008
    Yes you can write a recursive stored procedure. Bear in mind that this could have performance issues. Can you not get the date first with a different statement?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices