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

Answer 1

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.

• LatentDenis: This is perfect, thank you! Out of curiosity, is this the most efficient way of doing this?
• Gordon Linoff: @VolcovMeter . . . This is very reasonable. If you have a large table and many values are 0, then you might be concerned about performance.