SQL ORDER BY RANDOM
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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 theTABLESAMPLE
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:
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 theDBMS_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 auniqueidentifier
data type representing a 16-byte GUID. Hence, each new value returned by theNEWID
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 thedouble 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!
I'm running an online workshop on the 11th of October about High-Performance SQL.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.
