I have a table of teams that is filled with bot teams and player made teams. I have an endpoint that grabs 15 of these teams randomly. I want to prioritize grabbing player made teams instead of bot made teams.
I know I can get this to work by splitting it into two endpoints (one that grabs player made teams, and if it isn't enough, grabs bot teams to fill the remaining slots), but I wanted to know if it is possible to do it in one statement. What I'm looking for (if it exists) is a statement that will focus on grabbing player made teams first, and if it doesn't have 15, grabs bot teams.
Below is the statement I'm currently using. It sorts the players into their teams and calculates each team's overall rating. It then combines the ratings and teams and only picks those that are within -12/+12 rating, and randomly chooses 15.
SELECT owner, name, wins, losses, year, week
FROM (
SELECT user_id, FLOOR(AVG(ovr)) as team_ovr
FROM (
SELECT user_id, FLOOR((agility + strength + intelligence) / 3) AS ovr
FROM public.userplayer
)
GROUP BY user_id
)
INNER JOIN public.gbteam ON owner = user_id
WHERE team_ovr >= {base - 12} AND team_ovr <= {base + 12}
ORDER BY RANDOM()
LIMIT 15;
Assuming there is a variable in the gbteam table called 'is_bot', how do I prioritize this?
It should also be noted that I DO need RANDOM() for this SQL statement, so please no solutions saying "just remove random and do 'ORDER BY is_bot = False'".
Use both, first order by the player made teams and second by random. Something like this:
ORDER BY is_bot = False, RANDOM()