Group By problem - need to get most recent rows for unique column combinations

My table structure (in SQL Server) looks something like this: (D1 is more recent than D2, PK is a normal Identity(1,1) column)

Name  Type  Score  Date
A1    B1    C1     D1
A1    B2    C2     D1
A1    B1    C3     D2

What I need to do is find the latest Score values for each unique combination of Name and Type, i.e.:

A1    B1    C1     D1
A1    B2    C2     D1

I had originally done this by just using yesterday's date, but not everything is updated daily so sometimes scores were missing. I can get the unique combinations I need to look at with a simple

SELECT Name, Type FROM Table GROUP BY Name, Type ORDER BY MAX(Date)

but I obviously can't add the other two columns or the 开发者_如何学Cgroups are no longer unique.

I've had a look at similar questions but they all have differences that make them less useful for me.

Any help is much appreciated. I have a feeling that it's a fairly simple problem and that I just don't know enough to figure it out!

SELECT s.Name, s.Type, s.Score
FROM (
    SELECT Name, Type, MAX(Date) AS MaxDate
    FROM Scores
    GROUP BY Name, Type
) m
INNER JOIN Scores s ON m.Name = s.Name AND m.Type = s.Type AND m.MaxDate = s.Date

Standard (ANSI) SQL solution:

SELECT name,
       type, 
       score,
       Date,
FROM ( 
   SELECT name,
          type, 
          score,
          Date,
          row_number() over (partition by name, type order by Date desc) as rn
   FROM your_table
) t
WHERE rn = 1

Depending on your DBMS you might need to quote the column names "Type" and "Date" as they are reserved words.

As far as I can tell, the best way to achieve this would be with a self-join, if it has to be Database-agnostic.

SELECT name, type, date, t2.score
  FROM (SELECT   name, type, MAX (date) date
            FROM  testdata
        GROUP BY name, type) t1
       JOIN
       testdata t2 USING (name, type, date)