Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Select last occurrence of unique attributes

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 should have mentioned UserId sooner

EDIT 2: I need to get the latest scores by attribute for every user and average their scores over attributes. The sample looks like this:

   Date   |  UserId  | AttributeId | Score |
4/4/2013        1           1         0.64      
4/6/2013        1           1         0.35
4/6/2013        2           1         0.86
4/4/2013        1           3         0.34
4/4/2013        2           3         0.23
4/7/2013        2           1         0.54
4/8/2013        1           5         0.69
4/4/2013        2           4         0.27
4/7/2013        2           2         0.54
4/9/2013        1           4         0.66
4/9/2013        2           2         0.58
4/10/2013       1           4         0.66
4/9/2013        1           2         0.33
4/11/2013       2           5         0.10

first result looks like this:

   Date   |  UserId  | AttributeId | Score |
4/6/2013        1           1         0.64      
4/9/2013        1           2         0.33
4/4/2013        1           3         0.34
4/10/2013       1           4         0.66
4/8/2013        1           5         0.69
4/7/2013        2           1         0.86
4/9/2013        2           2         0.58
4/4/2013        2           3         0.23
4/4/2013        2           4         0.27
4/11/2013       2           5         0.10

And the last result after averaging scores by UserId:

UserId | AverageScore |
   1           0.532     
   2           0.408 
like image 442
ttkalec Avatar asked Dec 20 '22 06:12

ttkalec


1 Answers

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
like image 153
Quassnoi Avatar answered Dec 29 '22 01:12

Quassnoi