Returning Newest Date - 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)

Returning Newest Date

Author

Topic

Ellie
Starting Member

3 Posts

Posted - 2006-11-08 : 11:05:32

Hi,

I'm new to SQL Queries, and this is giving me a headache, so I wondered if someone may be able to help?

I have a table that stores reports for server disk information, and I need to pull back the latest dated report for each diskdrive in the given server, and ignore all the older ones. Each server can have multiple disks, and the date record is set against each disk, but is usually the same for each disk in a server. so after some searching the web, I tried:-

SELECT MAX(ReportDate), DiskID FROM DiskTable

WHERE servername = 'Server1'

GROUP BY DiskID

Now this worked fine, and returned the correct records for server1, in this case 2 records as the server had 2 disks.

But, this would be good if I only wanted the disk ID and report date, but I also need a lot of other fields from the DiskTable table like disksize, freespace, percentused and when I add them into the SELECT statement as shown below I get multiple records back for each disk instead of just 1.

SELECT MAX(ReportDate), DiskID, DiskSize, FreeSpace,PercentUsed FROM DiskTable

WHERE servername = 'Server1'

GROUP BY DiskID, DiskSize, FreeSpace,PercentUsed

I also tried a different apporoach using:-

SELECT servername, diskID reportdatete,DiskSize, FreeSpace,PercentUsed FROM Disktable

WHERE reportdate = (SELECT MAX(reportdate) FROM DiskTable)

but this only seems to work for the server that has the latest report date against it, and when I try and add

AND servername = 'Server1' to the where clause I get a syntax error.

What I need to be able to say is "Select the records with the latest report date, for each disk for this particular server" but dont seem to be able to accomplish it!

Any help would be appreciated.

Thanks, Ellie

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-08 : 11:18:30

Try this?

SELECT servername, diskID reportdatete,DiskSize, FreeSpace,PercentUsed FROM Disktable

WHERE reportdate = (SELECT MAX(reportdate) FROM DiskTable WHERE ServerName = 'Server1') and servername = 'Server1'

EDIT: oops

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Ellie
Starting Member

3 Posts

Posted - 2006-11-08 : 11:32:32

When I try:-

SELECT servername, diskID reportdatete,DiskSize, FreeSpace,PercentUsed FROM Disktable

WHERE reportdate = (SELECT MAX(reportdate) FROM DiskTable WHERE ServerName = 'Server1') and servername = 'Server1'

It gives peculiar results. If server1 exists in the server column it returns all records regardless of the servername that have the latest date. If I substitute 'Server1' with a servername not in the servername column, I get no results at all.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-08 : 12:06:40

Ok, follow the first link in my signature, and restate your question here with the information the link asks for.
You will get help pretty fast.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Ellie
Starting Member

3 Posts

Posted - 2006-11-09 : 06:00:34

Having thought about the results I was getting last night, I tried the suggested solution again this morning, and weirdly, without changing anything ( except server and workstation reboots ) it now works fine!

Thank you very much for your help

Ellie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources