Selecting max/min value from more than one fields

In the开发者_JS百科 following query the start/finish columns are datetime fields.

How should I modify this query to get two more columns, one with the min date and one with the max date (of all the 6 datetime fields and all the rows) repeated in each row.

Alternatively how could I create a new query returning only these 2 (min/max) dates, for the same resultset of course?

Thanks a lot! (I would like answers for both SQL Server 2005 and Sybase ASE 12.5.4)

select  erg_mst.code, 
        erg_types.perigrafh, 
        erg_mst.FirstBaseStart, 
        erg_mst.FirstBaseFinish,
        erg_mst.LastBaseStart, 
        erg_mst.LastBaseFinish ,
        erg_mst.ActualStart, 
        erg_mst.ActualFinish 
from    erg_mst inner join 
        erg_types  on erg_mst.type = erg_types.type_code  
where   erg_mst.activemodule = 'co' 
and     (
            FirstBaseStart <> NULL OR 
            FirstBaseFinish <> NULL OR  
            LastBaseStart <> NULL OR 
            LastBaseFinish <> NULL OR 
            ActualStart <> NULL OR 
            ActualFinish <> NULL 
        )  
order by    isnull(FirstBaseStart,isnull(LastBaseStart,ActualStart))

---

See below for a SQL Server 2005 code sample using Miles D's suggestion of using a series of UNION'ed selects (sorry, I don't know Sybase syntax):

select min(AllDates) as MinDate, max(AllDates) as MaxDate
from
(
select erg_mst.FirstBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseStart IS NOT NULL
union all
select erg_mst.FirstBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     FirstBaseFinish IS NOT NULL
union all
select erg_mst.LastBaseStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseStart IS NOT NULL
union all
select erg_mst.LastBaseFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     LastBaseFinish IS NOT NULL
union all
select erg_mst.ActualStart as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualStart IS NOT NULL
union all
select erg_mst.ActualFinish as AllDates
from    erg_mst 
where   erg_mst.activemodule = 'co'
and     ActualFinish IS NOT NULL
) #Temp

---

I can think of two solutions, but both will need to take on board Lucer's comment to use IS NOT NULL, rather than <> NULL.

2. Create a two user defined function to return the max and minimum values - ok, but assumes you have access to do it.
3. Use a series of UNION'ed selects, each one selecting one of the six columns and then use this as the inner nested SELECT where you then use SELECT MAX(), MIN() from that.