2

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?

  • Hi @Martin Lawrence and welcome to the site. I made a few edits to clean up the formatting. For future questions be sure to take advantage of the code formatting feature. It's also preferred, but not required, to post your sample data as SQL so people trying to answer your question can easily work with the data. I generated the tables with this site: senseful.github.io/web-tools/text-table – Joe Obbish Feb 22 '17 at 3:45
  • I did spend a bit of time trying to make the formatting work, but I gave up. I won't post again without proper formatting. Thank you for the site reference! – Martin Lawrence Feb 22 '17 at 4:31
3

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.

  • Joe Obbish, thank you for your answer - it worked perfectly. Now I have to research exactly why so I know what to do in the future. Thank you for providing a direction of study! – Martin Lawrence Feb 22 '17 at 4:29
  • Joe, can you provide a quick explanation of what the "t" does in the query after the ()? Just so I know what to Google about. Thanks! – Martin Lawrence Feb 22 '17 at 4:48
  • @MartinLawrence I need to give the derived table an alias or the query would error out. Try it without the "t". I just used t because I'm lazy and I didn't think this query needed a good alias because the only other thing going on was the WHERE clause filter. In a more complex query, such as one with the derived table joined to multiple other tables, I probably wouldn't use "t". – Joe Obbish Feb 22 '17 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.