SQL Server RANK Function – Return Rank of One Record vs Entire Table

ranksql server

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?

Best Answer

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.

Related Question