select rank and count

Question

I've got a table of user profiles and I'm trying to get user's profile along with their rank in the system based on their exp as well as how many users are there. The goal for me is to eventually print that this user is say... "ranked 5th of 135 people on the server". I can get rank(), but couldn't figure out how to get max(rank()) or just a count() along with it, since it requires group by, but by grouping I'm not allowed to get meaningfull count(). Here is what I tried:

select profile_ranked.*, max(profile_ranked.rank)
from (
    select
           *,
           rank() over (order by exp desc, id desc) as rank
    from profile
    where server_id = 6410
    ) as profile_ranked
where user_id = 1003

This throws error, saying gouping is required which ultimately defeats the goal. Another thing that I tried. This time with count():

select profile_ranked.*
from (
    select
           *,
           rank() over (order by exp desc, id desc) as rank,
           count(*) as count
    from profile
    where server_id = 6410
    group by id
    ) as profile_ranked
where user_id = 1003

The output in the column count is always 1, since I'm forced to group by id. Otherwise code doesn't even execute, throwing error.

I can do 2 queries that triverse the table twice, yes, but It doesn't make sense to do so, if all I need to do is to get result of rank() and max(). So I'm looking for a way to solve this in more of an elegant manner. Basically, I understand that I need to append the same value of count() or max() to every row. That'd solve the issue. But I have no idea how to do that. Any help will be appretiated.

Answer 1

You want a window count in the subquery:

select *
from (
    select p.*,
        rank() over (order by exp desc, id desc) as rn,
        count(*) over() as cnt
    from profile
    where server_id = 6410
) as p
where user_id = 1003