CODEWITHSUNDEEP
sqloracle-databasebigdata
Svarmit
Svarmit

Reputation: 1

Very huge data avoid analytical function

Assume there are 100 million distinct SID values in this table.

Example: Table test, columns SID, date_operation, score

So in this table, score changes everyday so if I want to get report of all the SID with most recent score. Don't want to use analytical function as otherwise cost would be very high. Tried self join also but looks like that is also costly.

If this question is redundant please direct me to similar question I will delete it.

Upvotes: 0

Views: 86

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

One method is:

select t.*
from t
where t.date = (select max(t2.date) from test t2 where t2.sid = t.sid);

This can take advantage of an index on test(sid, date).

However I have found good performance in Oracle with keep:

select sid, max(date),
       max(score) keep (dense_rank first order by date desc) as most_recent_score
from test
group by sid;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142788

select sid, max(date_operation)
from test
group by sid

will return what you asked for:

get all the Sid with most recent score

Upvotes: 1

Related Questions