Selection of Max value from 3 different columns?

  • I have a table with the following columns...

    ScheduleTime1 datetime,

    ScheduleTime2 datetime,

    ScheduleTime3 datetime.

    What I want to do is select the value with the latest ScheduleTime from the 3 possible columns. For example...

    ScheduleTime1 = 4/4/2005 10:00am

    ScheduleTime2 = 4/7/2005 11:00am

    ScheduleTime3 = 4/7/2005 11:32am

    In the example above I would want the ScheduleTime3 value. What is the best way in SQL to get the MAX date from ANY of the 3 fields?

    Some rules:

    1. There is no guarantee ScheduleTime2 is greater than ScheduleTime1. (Same goes for 2 & 3)

    2. Don't suggest to have just one ScheduleTime field - that's not an option.

    Thanks,

    Kyle

  • Is it just for 1 row, or is it the aggregate MAX function you are talking about?

    For 1 row:

    SELECT

    CASE WHEN ScheduleTime1 > ScheduleTime2 THEN

    CASE WHEN ScheduleTime1 > ScheduleTime3 THEN ScheduleTime1

    ELSE ScheduleTime3

    END

    ELSE

    CASE WHEN ScheduleTime2 > ScheduleTime3 THEN ScheduleTime2

    ELSE ScheduleTime3

    END

    END

    FROM ...

  • And just add the MAX aggregate function around the CASE if that is what you were looking for.

  • You could also implement as a simple pivot using a Union of 3 queries in a derived table:

    Select PKeyColumn(s), Max(ScheduleTime) As MaxScheduleTime

    From

    (

      Select PKeyColumn(s), ScheduleTime1 As ScheduleTime

      From YourTable

      Union All

      Select PKeyColumn(s), ScheduleTime2 As ScheduleTime

      From YourTable

      Union All

      Select PKeyColumn(s), ScheduleTime3 As ScheduleTime

      From YourTable

    ) dt

    Group By PKeyColumn(s)

     

  • True, but at least for this example that would have much worse performance, since it would require three scans as opposed to one.

  • Of course if the data structure was better normalised it would be easier - i.e. the ScheduleTime's should be in a linked table if there is more than one possible ScheduleTime - then you have a one to Many relationship - e.g. main table called say SCHEDULETASKS and then create sub table called say SCHEDULETIMES then you could have e.g.

    SCHEDULETASKS ID,..... etc. whatever cols you need but ID = unique key

    SCHEDULETIMES ID,SCHEDULETASKS_ID,SCHEDULETIME etc

    and then just SELECT STASK.ID,MAX(STIME.SCHEDULETIME) FROM SCHEDULETASKS STASK INNER JOIN SCHEDULETIMES STIME ON

    STASK.ID=STIME.SCHEDULETASKID GROUP BY STASK.ID

  • I agree that the *best* advise here is to properly design your schema. However, if you are stuck with this design, what about one of these?

    SET NOCOUNT ON

    IF OBJECT_ID('max_t') > 0

     DROP TABLE max_t

    GO

    CREATE TABLE max_t

    (

     col1 INT

     , col2 INT

     , col3 INT

    )

    INSERT INTO max_t(col1, col2, col3) VALUES(1,3,6)

    INSERT INTO max_t(col1, col2, col3) VALUES(12,3,9)

    INSERT INTO max_t(col1, col2, col3) VALUES(0,25,8)

    INSERT INTO max_t(col1, col2, col3) VALUES(5,NULL,30)

    SELECT CASE

     WHEN MAX(col1) > MAX(col2) THEN

      CASE

       WHEN MAX(col1) > MAX(col3) THEN MAX(col1)

       ELSE MAX(col3)

      END

     WHEN MAX(col2) > MAX(col3) THEN MAX(col2)

     ELSE MAX(col3)

    END

    FROM max_t

    SELECT CASE

     WHEN MAX(col1) >= MAX(col2) AND MAX(col1) >= MAX(col3) THEN MAX(col1)

     WHEN MAX(col2) >= MAX(col1) AND MAX(col2) >= MAX(col3) THEN MAX(col2)

     ELSE MAX(Col3)

    END

    FROM max_t

    SELECT CASE

     WHEN Max1 >= Max2 AND Max1 >= Max3 THEN Max1

     WHEN Max2 >= Max1 AND Max2 >= Max3 THEN Max2

     ELSE Max3

    END

    FROM

    (SELECT MAX(col1) Max1, MAX(col2) Max2, MAX(col3) Max3

     FROM max_t) x

    DROP TABLE max_t

    SET NOCOUNT OFF

    Result is always

               

    -----------

    30

    Should be easy to adapt it to use a DATETIME.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • >However, if you are stuck with this design, what about one of these?

    ...

    >Result is always : 30

    Yes, they all evaluate to the same execution plan. Which is almost the same plan as the one for my example (if the MAX is added), except that in mine the compute scalar operation is done in the arguments for stream aggregate.

    Regarding the schema: Remember that without knowing the business rules we cannot say if this design is correct or not. This specific query would have been easier to write with a different design, that is true. But we do not know if this design describes something that has three specific pieces of date information, each with some special meaning (different from the other two), and always exactly three dates. In such a case it would probably be absolutely correct to design the schema this way. The column names and query usage in this case suggests otherwise, but I just wanted to point out that it is not necessarily wrong.

  • Yes, your

    SELECT

    CASE WHEN ScheduleTime1 > ScheduleTime2 THEN

    CASE WHEN ScheduleTime1 > ScheduleTime3 THEN ScheduleTime1

    ELSE ScheduleTime3

    END

    ELSE

    CASE WHEN ScheduleTime2 > ScheduleTime3 THEN ScheduleTime2

    ELSE ScheduleTime3

    END

    END

    FROM ...

    and the one I posted

    SELECT CASE

     WHEN MAX(col1) > MAX(col2) THEN

      CASE

       WHEN MAX(col1) > MAX(col3) THEN MAX(col1)

       ELSE MAX(col3)

      END

     WHEN MAX(col2) > MAX(col3) THEN MAX(col2)

     ELSE MAX(col3)

    END

    FROM max_t

    look pretty similar. Actually I've overseen your second reply in this thread while first reading.

    And yes, you're right that there are cases where such a deisgn would be appropriate. However, given the names of the columns and the comment by the OP "not to suggest to have just one column" can lead to the conclusion that there might be a design flaw present. Anyway, I think he has now some good working solutions.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Thanks for your reply guys. While I agree to a certain extent that the ScheduleTime could be broken out in to another table (with proper ref. integrity), we're trying to keep things relatively simple. Having the 3 columns works quite well for how we're using them. This question was related to a batch job I'm writing - that's how this issue came up.

  • Just for the fun of it allow me to introduce another point of view.

    Yeah the case statement may be more performant that PWs but I would choose PW for superior ease of Maintenace.

    Have you though how this would change if you get a couple more scheduleTime columns ?

     

     


    * Noel

  • Then we would be pretty sure that the design was probably not the best one. And the performance of the -- admittedly more manageable -- query that PW supplied would be increasingly worse.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply