0

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.

animuson
  • 53,861
  • 28
  • 137
  • 147
Paul
  • 1,041
  • 11
  • 26

1 Answers1

4

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
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • So in order to build my derived table, the derived table will require a derived table. I will give this a go, hopefully in the next hour or two. – Paul Apr 23 '12 at 14:02
  • Thanks for your reply but that won't work. I'm afraid the fault is mine however for not getting my question correct, I'm sure your answer was correct as phrased. Updated my question to be what I was actually trying to ask, that I want 1 row per userid and date with any valid OS matching those 2 criteria. – Paul Apr 23 '12 at 14:27
  • Got it sorted in the end, the real query is giving me a headache at this point. What I've done is similar to your answer but replacing "OS" with "OS = MAX(OS)". Basically nesting and repeating the original logic that I had. – Paul Apr 23 '12 at 15:15