mySQL - Select records based on value in order, then random records in one query

Question

I'm looking to have one mySQL query where I can select all records set to a particular value, then selecting a number of other records from the records left, in a random order.

To explain, here's an example: (MyTable database table)

ID        Name        SomeValue
1         Fred        0
2         Jake        0
3         Jone        1
4         Bill        0
5         Greg        0
6         Thom        2
7         Jane        3
8         Erin        0

So first, I'd want to select all records where SomeValue is more than 0. The appropriate mySQL query for this would be:

SELECT * FROM MyTable WHERE SomeValue > 0 ORDER BY SomeValue DESC

This would return:

7         Jane        3
6         Thom        2
3         Jone        1

Next (in the same query), how would it be possible to select 3 other records at random (limit 6), from the ones remaining?

So that the end result of records returned looks something like this:

7         Jane        3
6         Thom        2
3         Jone        1
5         Greg        0    // Randomly picked
1         Fred        0    // Randomly picked
8         Erin        0    // Randomly picked

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-03-27T20:50:21

Use union all:

(select t.*
 from t
 where somevalue > 0
) union all
(select t.*
 from t
 where somevalue = 0
 order by rand()
 limit 3
);

If you want the values ordered by somevalue add order by somevalue as the last line of the query.

mySQL - Select records based on value in order, then random records in one query

1 Answers