1

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).

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.