select X.attempt, X.name, X.tot_score from
(select attempt,name,sum(score) tot_score
from TABLE group by attempt,name) X
(select name, MAX(attempt) from TABLE
group by name
Although this SQL works fine, but takes forever to run because the sample TABLE I have used in my example as an abstraction is in reality a highly complex join in itself. Is there anyway I could achieve similar results by avoiding the self join here and hence make my query faster?
I wish I could do that
The attempt sequence however is sequentially generated and I can't select for 'a particular' attempt. I'm looking for a generic solution which picks up the last attempt, whatever value that is.
So the join is complex simply because it joins a couple of badly indexed tables having rows in the order of hundreds of million. Hence I was hoping to avoid another join in the end and see if that makes it a little faster. But thanks for the suggested query tonkuma, I'll lpost the performance improvements if any as soon as I run it.