Introduction

In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function.

This is a very handy trick, especially when you want to shuffle a given result set.

Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets.

If you have to shuffle a large result set and limit it afterward, then it’s better to use something like the Oracle SAMPLE(N) or the TABLESAMPLE in SQL Server or PostgreSQL instead of a random function in the ORDER BY clause.

Database table

Let’s assume we are developing a music player desktop application, and we have the following song table in our database.

The song table is populated as follows:

Using a RANDOM function in the SQL ORDER BY clause

We want to shuffle the song list so that each new playlist is different than a previously generated one. Whenever we need to sort a given SQL query result set, we have to use the ORDER BY clause.

However, to randomize the returned rows, we need the ORDER BY clause to use a function or database object that returns a random value for each row contained in the SQL result set.

As already mentioned, using a database-specific function that returns random values is suitable for small result sets only, as otherwise, the SQL query performance is going to be affected. Luckily, in our case, the song table represents the current playlist of a desktop application, hence the size of the result set is reasonable.

Oracle

On Oracle, you need to use the DBMS_RANDOM.VALUE function, as illustrated by the following example:

SELECT
    artist||' - '||title AS song
FROM song
ORDER BY DBMS_RANDOM.VALUE

When executing the SQL query above on Oracle, the following result set is obtained:

Notice that the songs are being listed in random order, thanks to the DBMS_RANDOM.VALUE function call used by the ORDER BY clause.

The VALUE function in the DBMS_RANDOM package returns a numeric value in the [0, 1) interval with a precision of 38 fractional digits.

SQL Server

On SQL Server, you need to use the NEWID function, as illustrated by the following example:

SELECT
    CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY NEWID()

When executing the SQL query above on SQL Server, the following result set is obtained:

Notice that the songs are being listed in random order, thanks to the NEWID() function call used by the ORDER BY clause.

The NEWID function returns a uniqueidentifier data type representing a 16-byte GUID. Hence, each new value returned by the NEWID function is going to be virtually unique.

PostgreSQL

On PostgreSQL, you need to use the random function, as illustrated by the following example:

SELECT
    artist||' - '||title AS song
FROM song
ORDER BY random()

When executing the SQL query above on PostgreSQL, the following result set is obtained:

Notice that the songs are being listed in random order, thanks to the random() function call used by the ORDER BY clause.

The random function returns a numeric value in the [0, 1) interval of the double precision type.

MySQL

On MySQL, you need to use the RAND function, as illustrated by the following example:

SELECT
  CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY RAND()

When executing the SQL query above on MySQL, the following result set is obtained:

Notice that the songs are being listed in random order, thanks to the RAND() function call used by the ORDER BY clause.

The RAND function returns a numeric value in the [0, 1) interval of a floating-point type.

JPA and Hibernate

When using JPA and Hibernate entity queries, you can just pass the random function to the ORDER BY clause as illustrated by the following JPQL query:

List<Song> songs = entityManager
.createQuery(
    "SELECT s " +
    "FROM Song s " +
    "ORDER BY random()", Song.class)
.getResultList();

The JPQL query above will work just fine for PostgreSQL, SQL Server, and MySQL since the random(), NEWID() or RAND() functions will be passed by Hibernate as-is to the SQL ORDER BY.

For Oracle, since the randomness is given by the DBMS_RANDOM.VALUE number generator, you will have to reference it as a SQL function (e.g., DBMS_RANDOM.VALUE()):

List<Song> songs = entityManager
.createQuery(
    "SELECT s " +
    "FROM Song s " +
    "ORDER BY DBMS_RANDOM.VALUE()", Song.class)
.getResultList();

That’s it!

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Randomizing the SQL query result set is a common requirement, so it’s useful to know what SQL function you can use in the ORDER BY clause depending on the underlying relational database type.

Insert details about how the information is going to be processed

DOWNLOAD NOW