Solution 1 (using TOP,
MAX[
^] and
UNPIVOT[
^])
SELECT TOP(1) FieldName, MAX(DateValue) AS MaxDate
FROM (
SELECT FieldName, DateValue
FROM (
SELECT a1, a2, a3, a4, b1, b2, b3, b4
FROM TableName
) AS pvt
UNPIVOT (DateValue FOR FieldName IN(a1, a2, a3, a4, b1, b2, b3, b4)) AS unpvt
) AS T
GROUP BY FieldName
ORDER BY DateValue DESC
Solution 2 (using
RANK[
^] and
UNPIVOT[
^])
CREATE TABLE #Integers (a1 INT, a2 INT, a3 INT, a4 INT,
b1 INT, b2 INT, b3 INT, b4 INT,
c1 INT, c2 INT, c3 INT, c4 INT)
INSERT INTO #Integers (a1, a2, a3, a4,
b1, b2, b3, b4,
c1, c2, c3, c4)
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
UNION ALL SELECT 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
UNION ALL SELECT 8, 5, 2, 7, 4, 1, 9, 6, 3, 1, 2, 3
UNION ALL SELECT 3, 6, 9, 1, 4, 7, 2, 5, 8, 9, 8, 7
SELECT FieldName, FieldValue
FROM (
SELECT FieldName, FieldValue, RANK() OVER(ORDER BY FieldValue DESC) AS [Rank]
FROM (
SELECT *
FROM #Integers
) AS pvt
UNPIVOT (FieldValue FOR FieldName IN(a1, a2, a3, a4,
b1, b2, b3, b4,
c1, c2, c3, c4)) AS unpvt
) AS T
WHERE [Rank]=1
DROP TABLE #Integers