An aside: On https://scotrail.datasette.io/scotrail/random_apology I see heavy use of:

    order by random() limit 1

I understand that in more or less all databases this is quite inefficient: it runs `random()` for all rows, sorts those random values and then chooses the row corresponding to the first one. In tables with millions of values this could be rather expensive.

Is there any generic way in SQL to cheaply choose an arbitrary row from a collection in a way that is not deterministic? [1] I’m thinking about something that has the same characteristics as Ruby’s `Array#sample`.

Postgres has TABLESAMPLE but that only works across an entire table so it’s not flexible enough to be useful in real world situations (ie where the collection to choose a random element from is already filtered by WHERE)

Or are there any databases that know how to recognize this `order by random()` idiom and skip the extra work? Kind of like how `count(*)` usually has optimized semantics to not actually look at the distinct values of each row.

[1] I realize that SELECT without an order clause is technically arbitrary but in practice it’s usually pretty deterministic.