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.
- Create a two user defined function to return the max and minimum values - ok, but assumes you have access to do it.
- 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.
精彩评论