Find Minimum of list of Fields 2

[Sacheveral]

What is the most efficient method to find the minimum/maximum value of a number of date fields in a single record? For example I would like something like:

select
field1,
field2,
field3,
field4,
(lowest of fields 1 to 4),
(highest of fields 1 to 4)
from
table1

 

[Otto]

A possible solution:

select f1, f2, f3, f4,
case
 when f1<f2 and f1<f3 and f1<f4 then f1
 when f2<f1 and f2<f3 and f2<f4 then f2
 when f3<f1 and f3<f2 and f3<f4 then f3
 else f4
end as lowest,
case
 when f1>f2 and f1>f3 and f1>f4 then f1
 when f2>f1 and f2>f3 and f2>f4 then f2
 when f3>f1 and f3>f2 and f3>f4 then f3
 else f4
end as highest
from table1

 

[SajidAttar]

Also can be achieve the same using union and aggregate functions..

Select min(Value) MinDate, max(Value) MaxDate
From (Select Field1 as Value From table1 
	Union all Select Field2 from table1
	Union all select field3 from table1
	Union all select field4 from table1) a

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[Sacheveral]

Thanks for those. The first works, but I was wondering if there was something a bit briefer, and more flexible in terms of the number of fields, for example maybe a system function in the form "minimum (field1, field2, field3, etc., etc.)"

The trouble with the second solution (unless I used a temp table or embedded it in the from statement) is that I only want one line returned per record, and also I am wanting a lot of other columns at the same time so the union could be quite complex.

Any suggestions for a simpler solution?

Thanks,

Joe