First, the max on scantime in line 1 is not required, because you are only getting one scantime value per server, thanks to the subquery. So your query becomes:
Code:
select Max(status),server,scantime
from Tbl_LotusServerDiskSpaceReport b
where scantime=
(select max(scantime)
from Tbl_LotusServerDiskSpaceReport a
where a.server=b.server
and a.percentused is not null
and a.AvailableSpace is not null)
and b.percentused is not null
and b.AvailableSpace is not null
group by server,scantime
order by server
Now,
if (server, scantime) form a unque key for the table then you don't need the max and group by at all:
Code:
select status,server,scantime
from Tbl_LotusServerDiskSpaceReport b
where scantime=
(select max(scantime)
from Tbl_LotusServerDiskSpaceReport a
where a.server=b.server
and a.percentused is not null
and a.AvailableSpace is not null)
and b.percentused is not null
and b.AvailableSpace is not null
order by server
I assume there is an index on the server column at least?
dBforums
>
Need to Optimize the SQl Query
09-29-04, 05:12
Linear Mode
