Real challange - avoid duplicates, - but on certain fields

ok gurus,

I've been knocking myself around with this challange, and are now to give up, or to bring in the brains of the real sql SQL-geeks :-)

I has table, - let's call it Pings.

It contains 4 fields: LicenseID (string), HostName (string), FolderName(string) and Pinged (datetime).

The table is populated automatically from software every x hour and as such, very often I have almost duplicate rows, except from the pinged. However, sometimes i have several pieces of software installated on multiple folders.

Now, - I want a select statement where I get all unique LicenseID/HostName/FolderName items, with the latest pinged time.

Any idea on how to do this from a single SQL statement?

select LicenseID,HostName,FolderName,Max(Pinged) as Pinged
from pings
group by LicenseID,HostName,FolderName

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

To fetch only the Latest Pinged Data..

Select Main.LicenseID,Main.HostName,Main.FolderName
From pings Main
Join(select Max(Pinged) as Pinged from pings) as Latest
on Latest.Pinged = Main.Pinged

To fetch all the Latest LicenseID,HostName,FolderName

Select Main.LicenseID,Main.HostName,Main.FolderName
From pings Main
Join(select LicenseID,HostName,FolderName,Max(Pinged) as Pinged
from pings group by LicenseID,HostName,FolderName) as Latest
on Latest.LicenseID = Main.LicenseID And Latest.HostName = Main.HostName
And Latest.FolderName = Main.FolderName And Latest.Pinged = Main.Pinged

|||

There you go:

CREATE TABLE #Test (
LicenseID varchar(255),
HostName varchar(255),
FolderName varchar(255),
Pinged datetime
)

Insert Into #Test (LicenseID, HostName, FolderName, Pinged)
SELECT '12345', 'HOSTNAME', 'FOLDER1', '01/02/07 13:42'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER1', '01/02/07 13:43'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER2', '01/02/07 13:30'
UNION SELECT '12345', 'HOSTNAME', 'FOLDER2', '01/02/07 13:31'

SELECT
A.LicenseID,
A.HostName,
A.FolderName,
Max(Pinged) As Latest
FROM
#Test A (NOLOCK)
GROUP BY
A.LicenseID,
A.HostName,
A.FolderName
ORDER BY
A.LicenseID,
A.HostName,
A.FolderName

|||

I've tested all three approaches and even though the two first is somehow the same, they all seem to work.

Thank you all very much!