Returning the largest value across columns in SQL Server 2008
In SQL Server 2008, I need to query the columns in T-SQL and return the largest number I can find (example below using dates, but the same applies for numbers). "NULL" indicates a null value, not a literal word (sorry if I am the obvious captain). The DesiredResultColumn column shows the final result I want to find in column 1 - column 3. I can't find an example here that suits exactly this.
ID Column1 Column2 Column3 DesiredResultColumn
001 1/1/2010 5/7/2011 8/12/2008 5/7/2011
002 7/1/2014 7/3/2012 10/12/2013 7/1/2014
003 9/1/2012 12/7/2012 NULL 12/7/2012
004 11/1/2012 NULL 8/12/2013 8/12/2013
Unfortunately my tables are not normalized due to the limitations of the original system, otherwise the max function will solve my problem. Thoughts? I appreciate it!
source to share
According to a similar question :
SELECT tbl.ID,
(SELECT MAX(Date)
FROM (VALUES (tbl.Column1), (tbl.Column2), (tbl.Column3)) AS AllDates(Date)) AS DesiredResultColumn
FROM tbl
Of course this only works on SQL 2008 and up, but you said you have 2008, so everything should be fine.
The good thing about this is when using CASE
or a similar expression, for example, as it is a little shorter and easier to read in my opinion. But it also processes values NULL
, so you don't need to think about them.
source to share
you can use the condition case
together with ISNULL()
to get the result as below (sample, did not include invalidation with ISNULL()
. You can enable this)
select ID,
Column1,
Column2,
Column3,
case when Column1 > Column2 and Column1 > Column3 then Column1
when Column2 > Column1 and Column2 > Column3 then Column2
else column3 end as DesiredResultColumn
from your_table
source to share
Test data
DECLARE @TABLE TABLE (ID VARCHAR(10), Column1 DATE, Column2 DATE, Column3 DATE)
INSERT INTO @TABLE VALUES
('001','1/1/2010','5/7/2011','8/12/2008'),
('002','7/1/2014','7/3/2012','10/12/2013'),
('003','9/1/2012','12/7/2012', NULL ),
('004','11/1/2012', NULL ,'8/12/2013')
Query
;WITH CTE
AS (
SELECT *
FROM @TABLE
UNPIVOT ( Dates FOR ColumnName IN (Column1, Column2,Column3) )up
)
SELECT t.ID , t.Column1, t.Column2, t.Column3, MAX(Dates) Highest_Date
FROM @TABLE t LEFT JOIN CTE C
ON t.ID = c.ID
GROUP BY t.ID , t.Column1, t.Column2, t.Column3
Result
βββββββ¦βββββββββββββ¦βββββββββββββ¦βββββββββββββ¦βββββββββββββββ
β ID β Column1 β Column2 β Column3 β Highest_Date β
β ββββββ¬βββββββββββββ¬βββββββββββββ¬βββββββββββββ¬βββββββββββββββ£
β 001 β 2010-01-01 β 2011-05-07 β 2008-08-12 β 2011-05-07 β
β 002 β 2014-07-01 β 2012-07-03 β 2013-10-12 β 2014-07-01 β
β 003 β 2012-09-01 β 2012-12-07 β NULL β 2012-12-07 β
β 004 β 2012-11-01 β NULL β 2013-08-12 β 2013-08-12 β
βββββββ©βββββββββββββ©βββββββββββββ©βββββββββββββ©βββββββββββββββ
SQL FIDDLE
source to share