EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ORDER BY Random

PostgreSQL ORDER BY Random

Priya Pedamkar
Article byPriya Pedamkar

Updated May 12, 2023

PostgreSQL ORDER BY Random

Introduction to PostgreSQL ORDER BY Random Function

PostgreSQL order by the random function returns the random number from the table using the order by clause. This, with an order by clause,e 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 a 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Below is the syntax.

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.

  • Select: You can use the SELECT statement with the order by random function in PostgreSQL to retrieve data from a table. 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: When using the order by random function in PostgreSQL, the column name used to retrieve data should be specified.
  • 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: You can use the order by clause with the random function in PostgreSQL to select a random value for retrieving data Order by clause will retrieve the random value from the specified table column, which we have used in the query.
  • From: To retrieve data from a specific table in PostgreSQL, the “SELECT” statement and the “FROM” keyword are used to specify the table.
  • Number: This is defined as a use number with the limit clause; it will retrieve a specific number of rows we used with the limit.
  • Limit: You can retrieve data from a table in PostgreSQL using the “LIMIT” clause and a number specifying the maximum number of rows to return. 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.

  • 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, it will return all rows from the table. The requested number of rows from the table will be returned if we use a limit with an order by clause.
  • The example below shows that if we have not used limits with an order by random function, it will return all rows from the table.

Code:

select * from stud2 order by random();
select * from stud2 order by random() limit 3;

PostgreSQL ORDER BY Random Example 1

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 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 that it will be slow as compared to other random methods in PostgreSQL.
  • PostgreSQL’s order by random function generates a random numeric value between 0 and 1, which is returned as a double-precision type.
  • Order by random clause is very useful and important in PostgreSQL when retrieving random records from the table in PostgreSQL.

Examples

Below are the examples.

Example #1

We have using random_test to describe the example of the order by random function as follows. Below is 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:

PostgreSQL ORDER BY Random Example 1

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:

Limit Clause Example 2

Example #3

The example below shows that order by random function 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:

Limit Clause Example 3

Example #4

The example below shows that order by random function using a specified column.

Code:

select id from random_test order by random () limit 5;

Output:

Specified Column Example 4

Example #5

The below example shows that order by random to find random numbers.

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:

PostgreSQL ORDER BY Random Example 5

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL ORDER BY Random” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Introduction to PostgreSQL Timestamp
  2. How to Notify Works in PostgreSQL?
  3. PostgreSQL JSON (Examples)
  4. Introduction to PostgreSQL Timestamp