title: How to select rows from a table with identical but random values in a column without knowing that value with only one query?
body: <p>Let's assume the following table and data:</p>
  
  <pre><code>CREATE TABLE `test_abc` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `val_abc` INT UNSIGNED NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
  );
  INSERT INTO test_abc (val_abc) VALUES
  (1), (2), (1), (1), (3), (2), (3), (2), (3), (3), (3);
  
  id | val_abc
  ------------
  1  | 1
  2  | 2
  3  | 1
  4  | 1
  5  | 3
  6  | 2
  7  | 3
  8  | 2
  9  | 3
  10 | 3
  11 | 3
  </code></pre>
  
  <p>Now I want to select all rows with the same <code>val_abc</code>, while it doesn't matter which <code>val_abc</code> it is - but since it shouldn't be always the same one, there needs to be a random factor. So basically randomly either all rows with <code>val_abc = 1</code> or all rows with <code>val_abc = 2</code> and so on. The values of val_abc can be anything (even non numeric) and are unknown at the code level. Expected results would be, for example:</p>
  
  <pre><code>id | val_abc
  ------------
  2  | 2
  6  | 2
  8  | 2
  
  id | val_abc
  ------------
  5  | 3
  7  | 3
  9  | 3
  10 | 3
  11 | 3
  </code></pre>
  
  <p>I could, of course, make 2 queries. One to get a random but existing value of <code>val_abc</code>, for example <code>SELECT val_abc FROM test_abc ORDER BY RAND() LIMIT 1</code>. And then a second query to get all rows with this specific value in <code>val_abc</code>. </p>
  
  <p>A second possibility would be to use a sub query, like <code>SELECT * FROM test_abc WHERE val_abc = (SELECT val_abc FROM test_abc ORDER BY RAND() LIMIT 1)</code>. But this actually doesn't work (MariaDB 5). The results seem to be totally random and different every time (1 row, 5 rows, 3 rows, mixed val_abc values and so on), what I don't fully understand yet. It looks like the combination of ORDER BY RAND and LIMIT won't work in a sub query as expected.</p>
  
  <p>Is there any possibility to achieve this with a sub select or even with a query without any sub queries?</p>