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
James Horsley
Workflow Consulting Limited -
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