Return rows in random order

Randomizing SQL Query Results: Techniques for Returning Rows in Random Order

Learn various SQL techniques to retrieve data in a random order, essential for applications requiring shuffled results like quizzes, featured items, or statistical sampling. This article covers methods for SQL Server and other SQL databases.

Returning rows in a random order is a common requirement in many applications. Whether you're building a quiz application, displaying a random selection of products, or performing statistical sampling, the ability to shuffle your query results is invaluable. This article explores several methods to achieve random ordering in SQL, focusing primarily on SQL Server, but also touching upon general SQL concepts applicable to other database systems.

The ORDER BY NEWID() Method (SQL Server)

For SQL Server, the most straightforward and commonly used method to return rows in a random order is to use ORDER BY NEWID(). The NEWID() function generates a unique GUID (Globally Unique Identifier) for each row. When you order by these unique, non-sequential values, the result set is effectively randomized.

SELECT column1, column2
FROM YourTable
ORDER BY NEWID();

Basic random ordering using NEWID() in SQL Server.

Limiting Random Rows with TOP and NEWID()

Often, you don't need to randomize the entire table, but rather select a random subset of rows. Combining TOP with ORDER BY NEWID() allows you to efficiently retrieve a specified number of random records. This is particularly useful for displaying a few random items on a homepage or in a 'related products' section.

SELECT TOP 10 column1, column2
FROM YourTable
ORDER BY NEWID();

Selecting 10 random rows using TOP and NEWID().

flowchart TD
    A[Start Query] --> B{SELECT TOP N ...}
    B --> C[FROM YourTable]
    C --> D{ORDER BY NEWID()}
    D --> E[Return Random Subset]
    E --> F[End Query]

Workflow for selecting a random subset of rows.

Alternative for Other SQL Databases: ORDER BY RAND()

Many other SQL databases, such as MySQL and PostgreSQL, provide a RAND() or RANDOM() function that can be used similarly to NEWID() for random ordering. The exact function name may vary, but the principle remains the same: generate a random number for each row and sort by it.

MySQL

SELECT column1, column2 FROM YourTable ORDER BY RAND();

PostgreSQL

SELECT column1, column2 FROM YourTable ORDER BY RANDOM();

Oracle

SELECT column1, column2 FROM YourTable ORDER BY DBMS_RANDOM.VALUE;