LISTING 1: Query to List Installed Software, Versions, and Dates

CREATE TABLE computer
(computer  nvarchar(15) NOT NULL,
software  nvarchar(50) NOT NULL,
version   nvarchar(50) NOT NULL,
datetimecol datetime NOT NULL,
errormessageno int NOT NULL)
GO
INSERT computer values ('Cmp01', 'Word', '97', '1/3/1999', 1)
INSERT computer values ('Cmp02', 'Word', '97', '2/4/1999', 1)
INSERT computer values ('Cmp01', 'Word', '2000', '8/9/1999', 2)
INSERT computer values ('Cmp01', 'Word', '2000', '10/9/1999', 1)

SELECT c.computer, c.software, c.version, c.datetimecol, c.errormessageno 
FROM computer AS c
JOIN (select computer, software, max(datetimecol) AS maxdate FROM computer GROUP BY computer, software) AS s
ON c.computer = s.computer AND c.software = s.software
WHERE c.datetimecol = s.maxdate
ORDER BY c.computer