sql server - Getting a results rank in TSQL -

sql server - Getting a results rank in TSQL -

i have stored procedure getting submission entries database along how much each entry has raised. trying "rank" of submission based on amount raised vs other submissions.

the sec homecoming should rank 1 because has higher totalraised amount first record should rank 2. close??

my sp:

select a.[id], a.[petname], a.[petcaption], b.[pettype], c.[firstname] ownerfirstname, c.[lastname] ownerlastname, d.[imagename], ( select convert(varchar(20),sum(transactionamount), 1) totalraised, rank() on (order sum(transactionamount) desc) rank petcontesttransactions submissionid = a.[id] , paymenttype = 'donation' xml path ('transactiondetails'), type, elements ) petcontestsubmissions bring together petcontesttypes b on a.[pettype] = b.[id] bring together emptable c on a.[empid] = c.empid bring together petcontestimages d on a.[image] = d.[submissionid] bring together petcontesttransactions e on e.[submissionid] = a.[id] e.[transactionstatus] = 'completed' , e.[paymenttype] = 'submission' xml path ('submission'), type, elements, root ('root');

xml returned:

<root> <submission> <id>1</id> <petname>nala</petname> <petcaption>shes wonder pup!</petcaption> <pettype>dog</pettype> <ownerfirstname>carl</ownerfirstname> <ownerlastname>h</ownerlastname> <imagename>nalahus123.png</imagename> <transactiondetails> <totalraised>130.00</totalraised> <rank>1</rank> </transactiondetails> </submission> <submission> <id>2</id> <petname>simba</petname> <petcaption>shes wonder pup!</petcaption> <pettype>cat</pettype> <ownerfirstname>carl</ownerfirstname> <ownerlastname>h</ownerlastname> <imagename>simbahus123.png</imagename> <transactiondetails> <totalraised>250.00</totalraised> <rank>1</rank> </transactiondetails> </submission> </root>

select a.[id], a.[petname], a.[petcaption], b.[pettype], c.[firstname] ownerfirstname, c.[lastname] ownerlastname, d.[imagename], rank() on (order sum(f.totalraised) desc) rank petcontestsubmissions bring together petcontesttypes b on a.[pettype] = b.[id] bring together emptable c on a.[empid] = c.empid bring together petcontestimages d on a.[image] = d.[submissionid] bring together petcontesttransactions e on e.[submissionid] = a.[id] outer apply ( select sum(transactionamount) totalraised, petcontesttransactions pt pt.submissionid = a.[id] , pt.paymenttype = 'donation' ) f e.[transactionstatus] = 'completed' , e.[paymenttype] = 'submission' xml path ('submission'), type, elements, root ('root');

i utilize outer apply totalraised , can rank directly.

sql-server tsql stored-procedures