SQL Select last occurrence of unique attributes

Question

I have a table that looks like this: ``` Date | AttributeId | Score | 4/4/2013 1 0.64 4/6/2013 2 0.35 4/6/2013 1 0.86 4/4/2013 5 0.34 4/4/2013 4 0.23 4/7/2013 3 0.54 4/8/2013 1 0.66 ``` Can I construct a query in MS SQL so that I get only the last occurrence of a particular AttributeID. From example above, the query would return this: ``` Date | AttributeId | Score | 4/6/2013 2 0.35 4/4/2013 5 0.34 4/4/2013 4 0.23 4/7/2013 3 0.54 4/8/2013 1 0.66 ``` If it helps, I know that there are only 5 AttributeIDs (1-5) and that they will not change. For the moment I'm querying the last 15 records (and hoping that those records contain all of my 5 attributes), and then on the application level, extracting the latest scores for all 5 attributes. Is there a better solution? EDIT: Also, if I have `Date`, `UserId`, `AttributeId` and `Score`, can I somehow group my results by `UserId`? When I tried your answers, I saw that I only get the latest results, no matter which `UserId`. I'm sorry guys, I sho…

Accepted Answer

```
SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY attributeId ORDER BY [date] DESC) rn
        FROM    mytable
        ) q
WHERE   rn = 1
```

If you have a fixed list of but a few attributes (or a table with them), this query might be more efficient:

```
SELECT  *
FROM    attribute a
OUTER APPLY
        (
        SELECT  TOP 1
                [date], score
        FROM    score s
        WHERE   attributeId = a.id
        ORDER BY
                [date] DESC
        ) s
```

Create an index:

```
CREATE INDEX
        ix_score_attribute_date__score
ON      score (attributeId, date)
INCLUDE (score)
```

for this query to work fast.

Update:

To select an average of latest scores per user, use this:

```
SELECT  userId, AVG(score)
FROM    (
        SELECT  userId, score
        FROM    (
                SELECT  *
                        ROW_NUMBER() OVER (PARTITION BY userId, attributeId ORDER BY [date] DESC) rn
                FROM    score
                ) s
        WHERE   rn = 1
        ) q
GROUP BY
        userId
```

SQL Select last occurrence of unique attributes

Solution

Problem