Reputation: 13
I have a table with following structure:
-----------------------------
|id|value|category|timestamp|
|1 |100 |1 |10 |
|2 |200 |1 |20 |
|3 |100 |2 |30 |
-----------------------------
How can I get the SUM of the LATEST values of different categories? So in this case I would like to get SUM of rows 2 and 3
Upvotes: 1
Views: 25
Reputation: 1271231
One method uses a correlated subquery:
select sum(t.value)
from t
where t.timestamp = (select max(t2.timestamp)
from t t2
where t2.category = t.category
);
With an index on (category, timestamp)
, this is likely the fastest method. But you can also use window functions:
select sum(t.value)
from (select t.*,
row_number() over (partition by category order by timestamp desc) as seqnum
from t
) t
where seqnum = 1;
Upvotes: 1