Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Lightbulb Unanswered: Selecting one of 3 columns...

    Hi,

    I have a table that has 3 different types of dates (date1, date2, date3), and they represent the edit times of the 3 different sections on the website.
    How do I select only one of those three that's the most recent? (It would represent the most recent edit on the website overall).
    It would be an easy task if it was only one column - I would simply select the MAX, but I have to be selective between 3 different columns and pick the one I "like".

    Thanks,

    NB

  2. #2
    Join Date
    Sep 2003
    Posts
    7
    Try this,

    SELECT
    MAX ( CASE
    WHEN DATE1 > DATE2 AND DATE1 > DATE3 THEN DATE1
    WHEN DATE2 > DATE1 AND DATE2 > DATE3 THEN DATE2
    WHEN DATE3 > DATE1 AND DATE3 > DATE2 THEN DATE3
    END )
    FROM
    Table

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    The expresion fails if Date1=Date2=Date3, so you need to have:

    SELECT
    MAX ( CASE
    WHEN DATE1 > DATE2 AND DATE1 > DATE3 THEN DATE1
    WHEN DATE2 > DATE1 AND DATE2 > DATE3 THEN DATE2
    ELSE DATE3
    END )
    FROM
    Table

    Alternatively, you can also consider to compute the three different maximums and (optionally) compute the maximum of these three dates:

    SELECT max(D) FROM (
    SELECT max(Date1) D, 'Date1' FROM <YourTable> UNION
    SELECT max(Date2) D, 'Date2' FROM <YourTable> UNION
    SELECT max(Date3) D, 'Date3' FROM <YourTable>
    ) T
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The MAX is only necessary if you want to find the most recent change across all of your records, which is not what I read from your original post.

    Note that all these complications occur because you are starting with a non-normalized data set. Such schemas almost always require complicated coding to work around their flaws. What are you going to do if sometime in the future you have to work with websites that have four or more sections?

    Do yourself a favor and fix the design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    51
    Very nice, thanks for the code!

  6. #6
    Join Date
    Mar 2004
    Posts
    51
    blindman, it is true that the coding is getting a bit out of control for this particular query. But... the current website structure, overall functionality needs, and types of records don't permit me to make it a different/better way. If I did change the structure to suit this particular query, it would probably make 50 other things much worse.

    But yes, I exactly get your point:
    Crappy design = Crappy & complex code = Crappy & slow system
    Last edited by nbozic; 12-29-04 at 11:48.

  7. #7
    Join Date
    Mar 2004
    Posts
    51
    I ran into some problems with the WHEN statements. If one or more of the 3 date fields are NULL, then the WHEN statements fail and the ELSE statement evaluates each time (because NULL values cannot be compared with date values in the query)

    It works fine if none of the dates are NULL - but I have to keep them as NULL by default if the sections on the website haven't been updated yet.

    Any ideas on how to get around this problem without adding many more WHEN statements?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the ISNULL() or COALESCE() functions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2004
    Posts
    51
    Excellent, exactly what I need! Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •