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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With