Display latest record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Display latest record

Author

Topic

richardsadler
Starting Member

1 Post

Posted - 2005-03-07 : 17:57:19

I have written a script to keep a track of diskspace on all our servers.
The script writes disk stats to table called freespace with the following columns

Computer,Drive,Disksize,Freespace,Percentage,Timestamp

Each server writes an entry every 10 minutes.

I need to get a web page that displays the latest data for each server partition being monitored.

select Computer,drive,max(timestamp) from freespace group by computer,drive order by computer

Gives me all the servers, their partitions and the time of the last entry.
BUt I want it to report back the above and some extra columns (Computer,Drive,Disksize,Freespace,Percentage,timestamp)

Any Ideas how you would do that?
I have been looking through the help and on the internet but cannot find anything.

PS using MSSQL 2000

Thanks,
Richard.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-07 : 19:20:05

select *
from freespace f
where timestamp = (select max(f2.timestamp) from freespace f2 where f2.computer = f.computer and f2.drive = f.drive)

select f.*
from freespace f
join (select computer,drive, timestamp = max(timestamp) from freespace group by computer,drive) f2
on f2.timestamp = f.timestamp
and f2.computer = f.computer
and f2.drive = f.drive

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources