SQL ORDER BY RANDOM

Sometimes, you may need to fetch random records from a database to display dynamic content like random articles, featured products, or suggestions. Based on the database system you’re using, SQL offers many ways to retrieve random rows. We will examine how to use SQL queries for several databases to retrieve random rows in this blog post.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Why Fetch Random Records?

Fetching random records from a database is useful in many scenarios, such as:

• Displaying random articles or blog posts to users.
• Showing random products in an e-commerce store.
• Generating quiz questions or random test cases.
• Providing random suggestions for users.

SQL Queries to Fetch Random Records

Each database system has its own method for fetching random records. The SQL queries for several databases are listed below:

MySQL:

SELECT column FROM table ORDER BY RAND() LIMIT 1;

PostgreSQL:

SELECT column FROM table ORDER BY RANDOM() LIMIT 1;

SQL Server:

SELECT TOP 1 column FROM table ORDER BY NEWID();

Oracle:

SELECT column FROM (SELECT column FROM table ORDER BY dbms_random.value) WHERE rownum = 1;

IBM DB2:

SELECT column, RAND() AS IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY;

Practical Example with MySQL

Let’s understand this concept with an example using MySQL. Consider a table “products” with the following data:

Table: products

ID	Product_Name	Stock_Quantity	Price	Added_Date
1	Laptop	10	50000	2023-09-12
2	Headphones	25	2000	2023-10-05
3	Smartwatch	15	7000	2023-08-20
4	Keyboard	30	1500	2023-07-15
5	Monitor	20	12000	2023-06-10

Suppose we want to retrieve a random product from the products table. We can use the following query:

SELECT * FROM products ORDER BY RAND() LIMIT 1;

Sample Output:

ID	Product_Name	Stock_Quantity	Price	Added_Date
3	Smartwatch	15	7000	2023-08-20

Now, let’s execute the same query again:

SELECT * FROM products ORDER BY RAND() LIMIT 1;

Another Sample Output:

ID	Product_Name	Stock_Quantity	Price	Added_Date
5	Monitor	20	12000	2023-06-10

From the above results, we can see that even though we executed the same query twice, we got different records each time. This is because the RAND() function selects random records dynamically every time the query is executed.

Fetching All Records in Random Order

If you want to retrieve all records in a random order, use the following query:

SELECT * FROM products ORDER BY RAND();

Sample Output:

ID	Product_Name	Stock_Quantity	Price	Added_Date
2	Headphones	25	2000	2023-10-05
4	Keyboard	30	1500	2023-07-15
5	Monitor	20	12000	2023-06-10
1	Laptop	10	50000	2023-09-12
3	Smartwatch	15	7000	2023-08-20

As seen in the output, the order of records changes each time you execute the query.

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

Fetching random records is a useful technique when displaying dynamic content in applications. Different databases provide different ways to achieve this. Using the ORDER BY RAND() function (or its equivalent in other databases), we can efficiently retrieve single or multiple random records in SQL.

For more such SQL tutorials, stay tuned to UpdateGadh!

---

SQL ORDER BY RANDOM
sql order by random postgresql
mysql order by random
sql order by random seed
sqlite order by random
snowflake sql order by random
oracle order by random
sql order by newid()
presto order by random
sql order by random row
sql order by random oracle