CODEWITHSUNDEEP
sqlmysqldatabase
Kenzie
Kenzie

Reputation: 1285

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

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!

Upvotes: 1

Views: 416

Answers (4)

ilitirit
ilitirit

Reputation: 16352

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 <columnlist>
from (select * from users order by max(rank) desc limit 0, 49 
      union 
      select * from users where user = x)

if you database supports it.

Upvotes: 0

Seibar
Seibar

Reputation: 70253

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

Upvotes: 0

Mariano
Mariano

Reputation: 1073

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.

Upvotes: 4

igelkott
igelkott

Reputation: 1287

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.

Upvotes: 1

Related Questions