lundi 27 mars 2017

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

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, 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




Aucun commentaire:

Enregistrer un commentaire