Fetch top X users, plus a specific user (if they're not in the top X)

Question

I have a list of ranked users, and would like to select the top 50. I also want to make sure one particular user is in this result set, even if they aren't in the top 50. Is there a sensible way to do this in a single mysql query? Or should I just check the results for the particular user and fetch him separately, if necessary?

Thanks!

Answer 1

If I understand correctly, you could do:

select * from users order by max(rank) desc limit 0, 49  
union  
select * from users where user = x

This way you get 49 top users plus your particular user.

Answer 2

Regardless if a single, fancy SQL query could be made, the most maintainable code would probably be two queries:

select user from users where id = "fred"; 
select user from users where id != "fred" order by rank limit 49;

Of course "fred" (or whomever) would usually be replaced by a placeholder but the specifics depend on the environment.

Answer 3

declare @topUsers table(
    userId int primary key,
    username varchar(25)
)
insert into @topUsers
select top 50 
    userId, 
    userName
from Users
order by rank desc

insert into @topUsers
select
    userID,
    userName
from Users
where userID = 1234 --userID of special user

select * from @topUsers

Answer 4

The simplest solution depends on your requirements, and what your database supports.

If you don't mind the possibility of having duplicate results, then a simple union (as Mariano Conti demonstrated) is fine.

Otherwise, you could do something like

select distinct from (select * from users order by max(rank) desc limit 0, 49 union select * from users where user = x)

if you database supports it.