I'm returning a list of results from a database but because of a design feature I need a specific order.

The results should return randomly. The only criteria is that one of the values should not appear twice in a row.

Here's the example data:

So I have a list of animals and their individual colours in the table. I want to return a list of 5 of these animals randomly ordered but without two colours show up in a row. So the dog can't show up after the mouse and the chameleon can't show up after the snake etc...

I have solved this with PHP in the past. But I'm looking for a faster and smarter solution and hopefully in MySQL only.

Let me know :-)

CodePudding user response：

Well, if you're using a recent version of MySQL (8.0 ), you can do something like this.

The first CTE term provides the data. You can replace that with any list of data you wish, directly from some table or the result of a complex query expression.

rn0 is the order of the randomly ordered data.

@Zakaria is correct. Here's the adjusted SQL to handle just the requirement that consecutive rows should not have the same color, after randomly ordering the data.

Basically, this randomly orders the data and then takes just the first edge of each color island, and limits the result to 5 islands.

WITH data (id,animals,color) AS (
        SELECT 1 AS id, 'hamster' AS animals  , 'brown' AS color  UNION
        SELECT 2, 'dog'       , 'brown'  UNION
        SELECT 3, 'horse'     , 'white'  UNION
        SELECT 4, 'mouse'     , 'gray'   UNION
        SELECT 5, 'cat'       , 'black'  UNION
        SELECT 6, 'bird'      , 'orange' UNION
        SELECT 7, 'snake'     , 'green'  UNION
        SELECT 8, 'monkey'    , 'orange' UNION
        SELECT 9, 'chameleon' , 'green'
     )
   , list1 AS (
        SELECT id, animals, color, ROW_NUMBER() OVER (ORDER BY rand()) AS rn0 FROM data
     )
   , list AS (
        SELECT *, CASE WHEN color = LAG(color) OVER (ORDER BY rn0) THEN 0 ELSE 1 END AS good
          FROM list1
     )
SELECT *
  FROM list
 WHERE good = 1
 ORDER BY rn0
 LIMIT 5
;

An example result:

 ---- ----------- -------- ----- ------ 
| id | animals   | color  | rn0 | good |
 ---- ----------- -------- ----- ------ 
|  9 | chameleon | green  |   1 |    1 |
|  2 | dog       | brown  |   3 |    1 |
|  6 | bird      | orange |   4 |    1 |
|  1 | hamster   | brown  |   5 |    1 |
|  3 | horse     | white  |   6 |    1 |
 ---- ----------- -------- ----- ------ 

The original SQL, which does more than requested, requiring distinct colors in the result. It's not what was requested.

WITH data (id,animals,color) AS (
        SELECT 1, 'hamster'   , 'brown'  UNION
        SELECT 2, 'dog'       , 'brown'  UNION
        SELECT 3, 'horse'     , 'white'  UNION
        SELECT 4, 'mouse'     , 'gray'   UNION
        SELECT 5, 'cat'       , 'black'  UNION
        SELECT 6, 'bird'      , 'orange' UNION
        SELECT 7, 'snake'     , 'green'  UNION
        SELECT 8, 'monkey'    , 'orange' UNION
        SELECT 9, 'chameleon' , 'green'
     )
   , list AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY rand()) AS rn0 FROM data
     )
   , step1 AS (
        SELECT list.*, ROW_NUMBER() OVER (PARTITION BY color ORDER BY rn0) AS rn
          FROM list
     )
SELECT *
  FROM step1
 WHERE rn = 1
 ORDER BY rn0
 LIMIT 5
;

Sample result:

 ---- --------- -------- ----- ---- 
| id | animals | color  | rn0 | rn |
 ---- --------- -------- ----- ---- 
|  7 | snake   | green  |   1 |  1 |
|  6 | bird    | orange |   2 |  1 |
|  3 | horse   | white  |   3 |  1 |
|  1 | hamster | brown  |   5 |  1 |
|  5 | cat     | black  |   6 |  1 |
 ---- --------- -------- ----- ---- 

CodePudding user response：

Do you mean something like this?

select any_value(name), color from animals group by color order by rand() limit 5;

Fiddle here: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ff1854c9698e2c60deaf9131ea87774c