I have a complicated query written on SQL Server 2000 which in part contains a join onto a derived table. This table is unfortunately not returning exactly how I desired as the underlying data differs to what I expected. Say the data are like this:
USERID,OS,DATEINSTALLED
237,win,01/01/1980
237,dos,01/02/1978
237,lin,08/08/2002
132,win,03/03/1982
132,dos,03/07/2002
132,mac,03/07/2002
Then my query looked as so:
SELECT USERID, DATEINSTALLED = max(DATEINSTALLED)
FROM INSTALLATIONS
GROUP BY USERID
Which would give a result set of
237,08/08/2002
132,03/07/2002
But what I require is a result set of:
237,lin,08/08/2002
132,dos,03/07/2002
OR
237,lin,08/08/2002
132,mac,03/07/2002
I'm not really fussed if it picks up mac or dos but it must not give 3 rows; as what I need is one row per userid, with a max date and "a" valid OS for that combination. So mac or dos are valid, but win is not (for user 132).
As it's a derived table as part of a more complicated query I need to keep it as clean and simple as possible due to execution time (source table is a few hundred thousand rows in size). As implied by the tags I'm limited to SQL Server 2000.
Try this:
SELECT USERID, OS, DATEINSTALLED
FROM INSTALLATIONS
JOIN (
SELECT USERID, DATEINSTALLED = max(DATEINSTALLED)
FROM INSTALLATIONS
GROUP BY USERID
) AS T
ON INSTALLATIONS.USERID = T.USERID AND INSTALLATIONS.DATEINSTALLED = T.DATEINSTALLED