intro
Let's learn how to get records from a query in random order by exploring the most common SQL order by random approaches in MySQL, PostgreSQL, SQL Server, and Oracle.
A common scenario when working with data is presenting it in random order to avoid bias or make it appear differently each time. This use case is so frequent that most major databases offer dedicated SQL order by random strategies.
In this guide, you will learn the most effective techniques to order by random in SQL, along with why this can be inefficient on large datasets.
Let’s dive in!
SQL Order By Random: What Does It Mean?
Using an SQL ORDER BY random strategy means telling your database to return the rows from a SELECT query in a random order rather than a predictable sequence. Under the hood, this mechanism works by assigning a random value to each row and then sorting the results by those values.
To order by random in SQL, the most popular databases expose specific functions or methods. This approach is useful for features like showing random products, articles, or quiz questions—but do not worry as we will learn more later in this article.
How to Order By Random in SQL: Top Approaches
The ANSI standard SQL does not define how databases should handle the “order by random” scenario. As a result, each database system implements that differently.
In this section, you will explore how to apply SQL ORDER BY random strategies in MySQL, PostgreSQL, SQL Server, and Oracle.
Note: The sample queries below will be executed in DbVisualizer, a top-rated database client with support for over 50 databases. However, any other multi-database client will work.
MySQL
This is how you can order by random in MySQL:
1
SELECT * FROM table_name
2
ORDER BY RAND();
In particular, the ORDER BY RAND() clause randomizes the order of returned rows by generating a random floating-point number for each row. In particular, the RAND() function returns a value between 0 (inclusive) and 1 (exclusive), and MySQL uses these values to sort the rows.
Behind the scenes, this is what happens:
See this approach in action in the sample query below:
1
SELECT * FROM employees
2
ORDER BY RAND();
Execute the query multiple times in DbVisualizer and observe how the order of the rows in the result set keeps changing:
The first time we ran the query, the selected employee IDs appeared in the order 3, 1, 2. The second time, they were in the order 1, 3, 2. Mission accomplished!
PostgreSQL
Below is the syntax for the SQL order by random approach in Postgres:
1
SELECT * FROM table_name
2
ORDER BY RANDOM();
In PostgreSQL, RANDOM() works just like RAND() in MySQL. As a result, the underlying logic for random ordering is essentially the same.
SQL Server
This is how you approach SQL order by random in SQL server:
1
SELECT * FROM table_name
2
ORDER BY NEWID();
The NEWID() function generates a unique uniqueidentifier value for each row. When used in the ORDER BY clause, SQL Server creates a different NEWID() per row, which results in a randomized order of the result set, as in the example below:
1
SELECT * FROM Users
2
ORDER BY NEWID();
Execute such a query multiple times in an SQL Server database client like DbVisualizer:
Notice how the result set changes at each run.
Note: The SQL Server RAND() function behaves differently compared to MySQL. So, this query will not work as in MySQL:
1
SELECT * FROM table_anme
2
ORDER BY RAND();
This will not produce random ordering. The reason is that RAND() is evaluated once per query, not once per row. So all rows get the same random number, and the result is not sorted.
Oracle
Here is the syntax to order by random in Oracle:
1
SELECT * FROM your_table
2
ORDER BY DBMS_RANDOM.VALUE;
In Oracle, the DBMS_RANDOM package provides a built-in random number generator.
Order By Random SQL Use Cases
Ordering by random rows in database management systems is especially useful in scenarios where unpredictability enhances user experience or testing. Common use cases include:
This strategy is often paired with LIMIT (or TOP equivalents) to retrieve only a small number of random rows (like 5, 10, or 15). In most scenarios, it is unlikely that you need to randomly sort the entire dataset.
Limitations of SQL Order By Random Strategies
Using ORDER BY RAND() and equivalent clauses is an intuitive way to randomize rows. At the same time, these SQL order by random strategies come with performance drawbacks—especially on tables with a lot of rows.
The main performance issue is that each function must be evaluated for every row, and then the database must sort all rows based on those random values. This process is CPU-intensive and prevents the database from caching the query results — that happens because each time we have a different outcome.
Additionally, adding a LIMIT (or equivalent) clause does not solve the issue. That is because ORDER BY is executed before LIMIT, meaning the entire dataset is still scanned and sorted randomly before truncating the result. So even if you are only selecting a few rows, the performance cost is incurred across the full table.
Conclusion
In this guide, you learned about SQL order by random strategies. In detail, you saw how they work, their syntax across different databases, and when to use them. As shown here, DbVisualizer makes working with these non-standard SQL approaches easier thanks to its support for over 50 databases. This tool also offers helpful features like SQL formatting, ERD-style schema diagrams, and query optimization tools. Download DbVisualizer for free today!
FAQ
Is it possible to order the results of a query randomly in SQL?
Yes, you can order query results randomly in SQL through ORDER BY clauses involving functions like RAND() in MySQL, RANDOM() in PostgreSQL, NEWID() in SQL Server, or DBMS_RANDOM.VALUE in Oracle. Each assigns random values to rows, which are then sorted to produce a randomized order.
What is the TABLESAMPLE statement, and how does it relate to randomness in SQL?
The TABLESAMPLE statement retrieves a sample of rows from a table without scanning the entire dataset. It is used for approximate random sampling by specifying a percentage or number of rows. As of this writing, it is supported only in SQL Server and PostgreSQL among major databases. Learn more in the official docs:
What are the most common order by random SQL challenges?
The most common SQL order by random SQL challenges are:
What Is a Faster Order by Random Approach?
Instead of sorting the entire table randomly, you can use a more efficient method to fetch a random row. In MySQL or PostgreSQL, try this:
1
SELECT * FROM table_name
2
LIMIT 1 OFFSET FLOOR(RAND() * (SELECT COUNT(*) FROM table_name));
This avoids sorting all rows and simply jumps to a random offset in the result set. It’s much faster, especially on large tables, though it may require two queries (or an SQL subquery).
Equivalently, in SQL Server, you can write:
1
SELECT TOP 1 *
2
FROM (
3
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
4
FROM table_name
5
) AS t
6
ORDER BY ABS(CHECKSUM(NEWID()));
This approach does not technically return rows in random order but extracts a random row efficiently without full-table sorting.
Why use a visual database client like DbVisualizer?
A visual database client like DbVisualizer simplifies working with databases by making it easy to manage, visualize, and query data. It offers an intuitive interface for handling tables, exploring schema relationships, and writing or debugging queries using a powerful built-in SQL editor. Specifically, features like autocomplete, ER diagrams, and data export tools set DbVisualizer apart. Try the Pro version with a 21-day free trial!
