Maximum date in a row (not a column)

Published 02 May 11 03:04 PM | MartinCroft

Day 2 of a post every day ! I was asked recently how you could get the latest/earliest date field across a row of data, not the most usual request it tends to be the maximum value in a column and then its just a straight forward GROUP BY and MAX in that case. However

maximumvalueinrow

given the example above the user wanted for each driver to take the minimum date of birth. the following solution could be used.

USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[DriverDetails]') AND type in (N'U'))
DROP TABLE [dbo].[DriverDetails]
GO
CREATE TABLE DriverDetails 
( DriverID INT IDENTITY(1,1),
  DriverName VARCHAR(20),
  Driver1DOB DATETIME,
  Driver2DOB DATETIME,
  Driver3DOB DATETIME
)  
INSERT INTO DriverDetails VALUES 
('Bob','01 May 1986','01 June 1986','01 April 1986'), 
('Mary','01 May 1976','01 June 1946','01 April 1986'), 
('John','01 May 1966','01 June 1956','01 April 1946'), 
('Jenny','01 May 1956','01 June 1966','01 April 1986') 
SELECT
      *
FROM
      DriverDetails a
JOIN  
(
--Unpivot the data and then do a MAX, MIN ON Driver
SELECT DriverID,MIN(DOB)AS MinDOB,MAX(DOB) AS MaxDOB
FROM 
   (SELECT DriverID,Driver1DOB,Driver2DOB,Driver3DOB
   FROM DriverDetails) p
UNPIVOT
   (DOB FOR Employee IN 
      (Driver1DOB,Driver2DOB,Driver3DOB)
)AS unpvt
GROUP BY DriverID
) b
ON a.DriverID =b.DriverID;
Filed under: ,

Comments

# simonsabin said on May 4, 2011 10:05 PM:

Its simpler to se this tirck and I think performs better sqlblogcasts.com/.../Neat-trick-to-find-max-value-of-multiple-columns.aspx

# MartinCroft said on May 7, 2011 12:37 PM:

I ran through Simon's suggestion against the code the user ran the initial query there was not a lot in it, the execution plan showed 51%, 49% in favour of using the union. IO STATISTICS were the same with the exception of the UNION creating a work table, but not actually making any difference

The table only had a small result set (45K rows when summarised)

The union query had to be amended with a derived table to allow grouping as when group got the message the date column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The SET STATISTICS TIME showed the union to be quicker, It was run against the production server so was unable to clear cache out, but switching the queries round still showed the UNION to be slightly quicker

Results

Pivot method

============

(44405 row(s) affected)

Table 'Pivot Table'. Scan count 17, logical reads 776563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  CPU time = 15840 ms,  elapsed time = 12791 ms.

Union Method

==============

(44405 row(s) affected)

Table 'Union'. Scan count 17, logical reads 776563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Warning: Null value is eliminated by an aggregate or other SET operation.

CPU time = 14051 ms,  elapsed time = 11956 ms.