Using RANK function, how can I return the rank of one record vs the entire table?

Question

I have the following info in my database:

+-----------------------+-------------+
| USERNAME        TRUST |             |
+-----------------------+-------------+
| DeaDTerra             |         390 |
| OgNasty               |         322 |
| Blazed                |         303 |
| monbux                |         260 |
| Tomatocage            |         230 |
| Stunna                |         220 |
| philipma1957          |         216 |
| John (John K.)        |         180 |
| CanaryInTheMine       |         173 |
| Mitchell              |         165 |
+-----------------------+-------------+

I want to run a query that will return the following:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          3    |
+----------------------+

I have tried the following query:

select top 25 username, trust, RANK() over (order by trust desc) AS Rank
from btcProfile 
where profile_name='Blazed' order by trust

All I get is this:

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          1    |
+----------------------+

How do I only pull up the one record but calculate the RANK() based on all rows in the table?

Answer 1

Put the window function in a derived table and move the filter outside of the derived table. That way the RANK() function will be calculated before the filter is applied instead of after. Here is one implementation:

CREATE TABLE #btcProfile (
profile_name VARCHAR(20) NOT NULL,
TRUST INT NOT NULL
);

INSERT INTO #btcProfile 
VALUES
('DeaDTerra', 390),
('OgNasty', 322),
('Blazed', 303),
('monbux', 260),
('Tomatocage',230),
('Stunna', 220),
('philipma1957', 216),
('John (John K.)', 180),
('CanaryInTheMine', 173),
('Mitchell', 165);

SELECT profile_name, [Rank]
FROM 
(
    select profile_name, trust, RANK() over (order by trust desc) AS [Rank]
    from #btcProfile
) t
where profile_name='Blazed';

Be sure that the RANK() function handles ties how you want it to. It wasn't clear how the TOP 25 related to your question. If you let me know I can integrate that into my answer.