# Find max and min date over multiple fields of the same record

I have a table with 4 distinct data-type fields. For each record, I need to find the max and min date over them.

That's easy to do for a set of records over a unique field. But how to do it in my case, without using cursor?

I'd need some sort of function that receives a list of fields, like `max(field1, field2, field3, field4)`.

If I understood you correctly, I'd use `CROSS APPLY` with `VALUES` to "unpivot" your fields and then a standard `MIN` and `MAX` can be used. Something like this:

``````DECLARE @T TABLE (ID int IDENTITY, f1 int, f2 int, f3 int, f4 int);

INSERT INTO @T (f1, f2, f3,f4) VALUES
(1, 2, 3, 4),
(5, 6, 7, 8),
(7, 8, 0, 1),
(2, 3, 6, 5);

SELECT ID, MIN(f) AS MinF, MAX(f) AS MaxF
FROM
@T
CROSS APPLY
(
VALUES (f1), (f2), (f3), (f4)
) AS CA(f)
GROUP BY ID
ORDER BY ID;
``````

Or without `CROSS APPLY`:

``````SELECT
ID
,(SELECT MIN(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MinF
,(SELECT MAX(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MaxF
FROM @T
ORDER BY ID;
``````

One more variation of the same theme. Aggregate within `CROSS APPLY`.

``````SELECT ID, MinF, MaxF
FROM
@T
CROSS APPLY
(
SELECT MIN(f) AS MinF, MAX(f) AS MaxF
FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)
) AS CA
ORDER BY ID;
``````

SQL Fiddle

All variants return the same result

``````+----+------+------+
| ID | MinF | MaxF |
+----+------+------+
|  1 |    1 |    4 |
|  2 |    5 |    8 |
|  3 |    0 |    8 |
|  4 |    2 |    6 |
+----+------+------+
``````

The last variant has a better-looking execution plan for this simple table.

• The cross apply worked, thanks a lot! I'm gonna try the subquery variant. – Hikari Nov 5 '15 at 14:57