0

I'm trying to do the following in MySQL (specifically MariaDB):

  • Out of a database, select n random rows (different rows should be selected every time the query is sent)
  • Return those rows in a sorted order (for example by uid or name)

All examples I found for returning a series of random rows (example1, example2, example 3) work by first sorting the table in a random order and then selecting n rows from that table, for example:

SELECT * FROM table ORDER BY RAND() LIMIT 10

The exact keyword used to achieve a random order differs by SQL system used (and there might also be several alternatives for some of them), but all solutions use the ORDER BY keywords.

The obvious problem with that is that it also scrambles the order of the table. However, I need the result in sorted order and I can't order the table in two different ways simultaneously.

I tried to see whether I could chain keywords to achieve the desired result by chaining ORDER BY like this:

-- first order by rand, then select 10, then order the result again
SELECT * FROM table ORDER BY RAND() LIMIT 10 ORDER BY name

However, SQL is a declarative, not an imperative language, so that was never going to work, at least not the way I tried it.

I'm out of ideas, is there a way to do this at all?

Improve this question
0

1 Answer 1

Reset to default
2

Try this code

SELECT t.*
FROM (
    SELECT id
    FROM table
    ORDER BY RAND()
    LIMIT 10
) AS random_ids
JOIN table t USING (id)
ORDER BY t.name;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

More efficiently would be to select only id in the inner query, then JOIN USING(id) when getting to the outer query. (Assuming PRIMARY KEY (id))

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.