Introduction to PostgreSQL ORDER BY Random Function
PostgreSQL order by the random function is used to return the random number from the table by using the order by clause. Random function with an order by clause will not work the same as the order by clause in PostgreSQL because the random function will pick the random values from the table in PostgreSQL. Order by clause using random function useful on large tables for retrieving the data faster, because order by the random function will return the random number from the table. We can also use order by random function using the limit clause, using the limit clause we have retrieving data from the table.
Syntax
Below is the syntax of the order by random in PostgreSQL.
Select name_of_column1, name_of_column2, name_of_column3, …., name_of_columnN from name_of_table ORDER BY RANDOM ();
Select name_of_column1, name_of_column2, name_of_column3, …., name_of_columnN from name_of_table ORDER BY RANDOM () limit number;
Select * (select all column from table) from name_of_table ORDER BY RANDOM () limit number;
Parameters of PostgreSQL ORDER BY Random
Below is the parameter description syntax of the order by random in PostgreSQL.
- Select: This is defined as select operations is used to retrieve data from the table by using the order by random function in PostgreSQL. We can retrieve data from a single column, multiple columns, and all columns from the specified table.
- Name of Column 1 to Name of Column N: It is defined as the column’s name, which was we have used to retrieved data from a column by using the order by random function in PostgreSQL.
- Name of Table: This is the table name from which we have retrieving data. The table name is an essential and useful parameter in order by a random function.
- Order by Random: This function is used to select a random value to retrieve data by using order by clause in PostgreSQL. Order by clause will retrieve the random value from the specified table column, which was we have used in the query.
- From: This is defined as the select the specified table from which we have retrieving data. From keyword is used to select the table in PostgreSQL.
- Number: This is defined as a use number with the limit clause; it will retrieve a specific number of rows which was we have used with the limit.
- Limit: The limit clause is used to retrieve data from the table using the number, which was we have used with the limit clause. This is the optional parameter in the syntax of the order by random function in PostgreSQL.
How ORDER BY Random Function Works?
Below is the working of the order by random in PostgreSQL.
- If we want the random data from the table, we will use the order by random function in PostgreSQL.
- If we have not used limits with an order by clause, then it will return all rows from the table. If we have used a limit with an order by clause, it will return the specified number of rows from the table.
- The below example shows that if we have not used limits with an order by random function, then it will return all rows from the table.
Code:
select * from stud2 order by random();
select * from stud2 order by random() limit 3;
Explanation:
- In the above first example, we have not used a limit clause with an order by random function; after not using the limit clause, it will return all rows from the table in PostgreSQL.
- In the above second example, we have used a limit clause with an order by random function; after using a limit clause, it will return the specified number of rows from the table which was we have defined in the query.
- Order by the random function will return the random number from the table we used in the query.
- If we need a specified number random list simultaneously, we have to use an order by random function on the table.
- Order by clause will sort all the data from the table, so it will be slow as compared to other random methods in PostgreSQL.
- Order by random function in PostgreSQL will return the numeric value in the interval of 0 and 1, which is obtained as the double-precision type in PostgreSQL.
- Order by random clause is very useful and important in PostgreSQL at the time when we have retrieving random records from the table in PostgreSQL.
Examples of PostgreSQL ORDER BY Random Function
Below is an example of the order by random function in PostgreSQL.
Example #1
We have using random_test to describe the example of the order by random function in PostgreSQL are as follows. Below are the count and table structure of the random_test table.
Code:
\d+ random_test;
select count (*) from random_test;
select * from random_test limit 1;
Output:
Example #2
The below example shows that order by random function without using the limit clause. In the below example, we have not used a limit clause to display all records from the random_test table.
Code:
select * from random_test order by random ();
Output:
Example #3
The below example shows that order by random function by using a limit clause. In the below example, we have used a limit clause to display a specified number of records from the random_test table.
Code:
select * from random_test order by random () limit 5;
Output:
Example #4
The below example shows that order by random function by using a specified column.
Code:
select id from random_test order by random () limit 5;
Output:
Example #5
The below example shows that order by random to find random numbers in PostgreSQL.
Code:
SELECT CASE WHEN id = 0 THEN 1 ELSE id END
FROM (SELECT ROUND(RANDOM() * (SELECT MAX(id) FROM random_test)) as id) as r;
Output:
Recommended Articles
This is a guide to PostgreSQL ORDER BY Random. Here we discuss the Introduction of PostgreSQL ORDER BY Random Function and its syntax along with parameters along with practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
