How to Select Random Rows in SQL Databases?

SQL random row selection method

Database queries usually involve retrieving specific data from a table. Selecting random rows is useful for scenarios such as sampling or data randomization. This article explains how to select random rows in Microsoft SQL Server (MSSQL) and explores similar methods in other popular SQL databases.

MSSQL Solution

To randomly select rows from a table in MSSQL, use the following query:

<code class="language-sql">SELECT TOP 5 Id, Name
FROM customerNames
ORDER BY NEWID()</code>

NEWID() function generates a unique random value used to sort the results. This ensures that rows are selected in a random manner. The TOP clause limits the number of rows returned to 5.

Alternatives to other databases

The following commands provide equivalent functionality in other SQL databases:

• MySQL:

<code class="language-sql">SELECT column
FROM table
ORDER BY RAND()
LIMIT 1</code>

• PostgreSQL:

<code class="language-sql">SELECT column
FROM table
ORDER BY RANDOM()
LIMIT 1</code>

• Oracle:

<code class="language-sql">SELECT column
FROM (
  SELECT column
  FROM table
  ORDER BY dbms_random.value
)
WHERE rownum = 1</code>

• SQLite:

<code class="language-sql">SELECT column
FROM table
ORDER BY RANDOM()
LIMIT 1</code>

Other notes

When selecting a random row, please note the following:

• Randomness varies by database implementation.
• The same query is executed multiple times, and the order of rows may be inconsistent.
• Limiting the number of rows to fetch (for example, using TOP or LIMIT) can reduce query time.

The above is the detailed content of How to Select Random Rows in SQL Databases?. For more information, please follow other related articles on the PHP Chinese website!