Featured image of post Random Db2 SQL query results?!

Random Db2 SQL query results?!

I needed a feature, I had never used before: Random SQL query results

For a future talk, I am currently tinkering with a SQL-only implementation of a tic-tac-toe game. The board is managed as a relational table. Visualization, computing next moves, checking for a winner, etc. are all performed using SQL. Possible positions for the next move are scored and the move with the highest score is picked. That’s what you would do as a (rationally acting) human. When there are equal options as shown below, you as human probably would just pick one of them, with some variation (so it does not get too boring).

Next move in tic-tac-toe

A program or machine, would just go with the first of the equally scored moves. That’s why I started to look into just picking one of the top scorers randomly.

Returning one row

SQL allows to return just a single row from a result set. Depending on the SQL dialect, it is either LIMIT or FETCH FIRST/NEXT. They can be combined with an OFFSET clause to iterate over a query result and fetch them “page by page”.

To only get a single row from the result set, it is either

FETCH FIRST 1 ROW ONLY

or

LIMIT 1

Random query results

Typically, I expect the result set to be in a certained order. Sometimes, no specific order is needed or, for performance reasons, result sets stay unsorted. In SQL, the ORDER BY clause offers plenty of options to sort the results in the right way. You can even specify where/how NULLs are returned. But how do I specify results to be in a random order?

The answer is rather simple. Just use RANDOM() in the order-by-clause: ORDER BY RANDOM(). Here is a small example which I constructed. It has four rows which are returned in random order:

with data as
  (select id, name 
   from (values(1,'one'),(2,'two'),(3,'three'),(4,'four')) 
        as d(id,name)) 
select id,name from data order by random();

For my game of tic-tac-toe, I ended up with the following combination. It sorts rows based on the highest score and forces a random order on rows with the same score. From the rows with the top score, it returns just one row (LIMIT 1).

order by score desc, random() limit 1

RANDOM function

Taking a look at RANDOM(), it is a synonym for RAND() in the SYSIBM schema. There is even another RAND() in the SYSFUN schema with slightly different and fewer features. Both can be initialised with a seed which determines how the sequence of random number starts. (If you always use the same seed, the sequence should be the same, thus not really random)

The function GENERATE_UNIQUE() produces a random result and guarantees uniqueness across all invocations.

Conclusions

Random results are not what you typically would expect or require from your database. For adding variations to a game, it is exactly what is needed. And it is simple to achieve in SQL.

If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.