sql - Possible to eliminate this sub query? -

i have documents table fields as

documentid int - pk , autoincrement  laststatuschangeddatetime - datetime  jurisdictionid - int  documentstatusid - int 

now want bring in computed column named canchangestatus. queue. if document first in it's status can changed. query:

select d.documentid,   (     select cast(case when d.documentid = v.documentid 1 else 0 end bit)         (         select top 1 documentid         documents         jurisdictionid = d.jurisdictionid             ,         documentstatusid = d.documentstatusid         order laststatuschangeddatetime      ) v ) canchangestatus     documents d 

index there on jurisdictionid , documentstatusid. culprit subquery - way trying calculate canchangestatus. there way can speed or convert subquery join because joins mostly faster subqueries?

you should able aggregate function (example on sqlfiddle):

select documentid,        case when row_number() on ( partition jurisdictionid, documentstatusid                                      order laststatuschangeddatetime )          = 1 1 else 0 end bit  documents