SQL ORDER BY RANDOM

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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 database table

The song table is populated as follows:

id artist title
1 Miyagi & Эндшпиль ft. Рем Дигга I Got Love
2 HAIM Don’t Save Me (Cyril Hahn Remix)
3 2Pac ft. DMX Rise Of A Champion (GalilHD Remix)
4 Ed Sheeran & Passenger No Diggity (Kygo Remix)
5 JP Cooper ft. Mali-Koa All This Love

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:

song
HAIM – Don’t Save Me (Cyril Hahn Remix)
Ed Sheeran & Passenger – No Diggity (Kygo Remix)
Miyagi & Эндшпиль ft. Рем Дигга – I Got Love
JP Cooper ft. Mali-Koa – All This Love
2Pac ft. DMX – Rise Of A Champion (GalilHD Remix)

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:

song
Miyagi & Эндшпиль ft. Рем Дигга – I Got Love
HAIM – Don’t Save Me (Cyril Hahn Remix)
2Pac ft. DMX – Rise Of A Champion (GalilHD Remix)
Ed Sheeran & Passenger – No Diggity (Kygo Remix)
JP Cooper ft. Mali-Koa – All This Love

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:

song
JP Cooper ft. Mali-Koa – All This Love
2Pac ft. DMX – Rise Of A Champion (GalilHD Remix)
HAIM – Don’t Save Me (Cyril Hahn Remix)
Ed Sheeran & Passenger – No Diggity (Kygo Remix)
Miyagi & Эндшпиль ft. Рем Дигга – I Got Love

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:

song
2Pac ft. DMX – Rise Of A Champion (GalilHD Remix)
JP Cooper ft. Mali-Koa – All This Love
Miyagi & Эндшпиль ft. Рем Дигга – I Got Love
Ed Sheeran & Passenger – No Diggity (Kygo Remix)
HAIM – Don’t Save Me (Cyril Hahn Remix)

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!

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.

FREE EBOOK

7 Comments on “SQL ORDER BY RANDOM

    • That’s a good question. I updated the article to include a section for JPA and Hibernate. Check it out!

      • Hi Vlad,

        I get,

        `org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        
        Caused by: java.sql.SQLSyntaxErrorException: FUNCTION db_name.random does not exist
        
      • This is a Spring problem. Most likely, they are rending the query using Criteria API and use the JPA FUNCTION.

        If you do it exactly as I wrote in the article, using the entityManager.createQuery, it will work like charm.

  1. Please don’t suggest doing that without a big fat disclaimer that doing so in ORDER BY (a O(N log N) operation) is supremely expensive! There are better means of getting random samples in quite a few databases, including using TABLESAMPLE

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.