Select multiple rows from one table

Question

I am trying to get random sets of rows from one table based on its rank.

To be specific, I want to semi-randomly select players(rows) from a pool(table) to create a team. Each player is ranked from 0 to 100. The team is randomly picked from sets of ranks(Eg. 6 random players from top 20%, 8 random players from top 60%, and 6 players from top 100%)

I've tried using union, but the calculation time increases linearly for each union statement added. (The pool is a result set of a query)

Is there any other approach I should take other than union?

Why are you distinguishing between different scopes (top 20%, top 60%, top 100%)? You want it to be random anyway. — Fabian Bigler, Feb 19 '14 at 19:50
Here are a couple [good](http://stackoverflow.com/questions/249301/simple-random-samples-from-a-sql-database) [posts](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) about selecting random rows. [This one](http://stackoverflow.com/questions/11595696/how-to-select-a-random-sample-of-records-from-mysql) uses `UNION`, but also touches on selecting within a certain class or grouping. — Patrick Q, Feb 19 '14 at 19:56
@FabianBigler Yes! It does make it less random. In short, I'm trying to make teams of mentor-mentee. I've added randomness so that players have less chance to be assigned to same team or team members the next round. — Jee Seok Yoon, Feb 19 '14 at 20:04
As a side note, I know that you're asking how to do this in a query, but if you're creating multiple teams, it may be faster to dump the list of players and ranks into an array and do all the team-creation work in code. That way you're only executing one query, and it would probably be easier to not select the same player for multiple teams. — Patrick Q, Feb 19 '14 at 20:27

mhoglan · Accepted Answer · 2014-02-19T21:18:48.330

Why don't you make a generated column to represent what band a player is in, and then do a rank based on that band also.

logic as such:

IF PLAYER_RANK < 20
  PLAYER_BAND = 1
ELSE IF PLAYER_RANK < 60
  PLAYER_BAND = 2
ELSE
  PLAYER_BAND = 3

Now that you have the player banded, you can then do a RANK on that BAND. That would give you an incrementing value for each player in the band. I may be Player Rank 50 overall, but I would be Rank 30 in Band 2

You can then in a where clause do your filtering

WHERE PLAYER_BAND = 1 and BAND_RANK <= 6 ....

That would limit your results to what you want, X people from each band. No union needed, maybe subselect to make the outer select simpler.

UPDATE:

Try something like this out.

SET @prevBand := null;
SET @rank := 0;

select 
    *
from
    (select 
        ranking,
            user,
            band,
            random,
            IF(@prevBand <> band, @rank:=1, @rank:=@rank + 1) as 'band_ranking',
            @prevBand:=band
    from
        (SELECT 
        ranking,
            CASE
                WHEN ranking < 2 THEN 1
                WHEN ranking <= 4 THEN 2
                ELSE 3
            END as 'band',
            user,
            rand() as 'random'
    FROM
        queries
    order by random) players order by band) player_ranks
where
    (band = 1 && band_ranking <= 1)
        || (band = 2 && band_ranking <= 1)
        || (band = 3 && band_ranking <= 2);

Mainly concentrated on the getting a band a rank within the band. The randomness can be achieved by generated a random value for each record and do an order in the rank for the bank — mhoglan, Feb 19 '14 at 20:10
"The randomness can be achieved by"… Could you show an example of this in your answer? Not implying that you're wrong, it would just be a more complete answer of the OP's question if it included (what I would consider to be) a crucial part of the question. — Patrick Q, Feb 19 '14 at 20:21
Added the SQL to do the randomness and banding and banding rank — mhoglan, Feb 19 '14 at 21:19

Select multiple rows from one table

1 Answers1