EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

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
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW