SQL ORDER BY RANDOM
If you want the resulting record to be ordered randomly, you should use the following codes according to several databases.
Here is a question: what is the need to fetch a random record or a row from a database?
Sometimes you may want to display random information like articles, links, pages, etc., to your user.
If you want to fetch random rows from any of the databases, you have to use some altered queries according to the databases.
- Select a random row with MySQL:
If you want to return a random row with MY SQL, use the following syntax:
- Select a random row with Postgre SQL:
-
Select a random row with SQL Server:
-
SELECT TOP 1 column FROM table ORDER BY NEWID ();
- Select a random row with oracle:
-
SELECT column FROM (SELECT column FROM table ORDER BY dbms_random.value) WHERE rownu
-
Select a random row with IBM DB2:
-
SELECT column RAND () as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY;
To understand this concept practically, let us see some examples using the MySQL database. Consider we have a table items created into the database with the following data:
Table: items
| ID | Item_Name | Item_Quantity | Item_Price | Purchase_Date |
|---|---|---|---|---|
| 1 | Soap | 5 | 200 2021-07-08 | |
| 2 | Toothpaste | 2 | 80 | 2021-07-10 |
| 3 | Pen | 10 | 50 | 2021-07-12 |
| 4 | Bottle | 1 | 250 | 2021-07-13 |
| 5 | Brush | 3 | 90 | 2021-07-15 |
Suppose we want to retrieve any random record from the items table.
We will write the query as follows:
We may get the following results:
| ID | Item_Name | Item_Quantity | Item_Price | Purchase_Date |
|---|---|---|---|---|
| 3 | Pen | 10 | 20 | 2021-07-12 |
Now let us try executing the same query one more time.
We may get the following results:
| ID | Item_Name | Item_Quantity | Item_Price | Purchase_Date |
|---|---|---|---|---|
| 5 | Brush | 3 | 90 | 2021-07-15 |
From the above results, we can conclude that we get different records as output both times even though we executed the same query twice. RAND () function has selected random records both times for the same query from a single table. Therefore, even we execute the same query again, we will get different output every time. There is a rare possibility of getting the same record consecutively using the RAND () function.
Now, suppose you want all the records of the table to be fetched randomly.
To do so, we need to execute the following query:
We may get the following results:
| ID | Item_Name | Item_Quantity | Item_Price | Purchase_Date |
|---|---|---|---|---|
| 4 | Bottle | 1 | 250 | 2021-07-13 |
| 5 | Brush | 3 | 90 | 2021-07-15 |
| 1 | Soap | 5 | 200 | 2021-07-08 |
| 2 | Toothpaste | 2 | 80 | 2021-07-10 |
| 3 | Pen | 10 | 50 | 2021-07-12 |
There is also a possibility of getting some different arrangements of records if we execute the RAND () function again on the employees table.
