EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ORDER BY Random
Secondary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions

PostgreSQL ORDER BY Random

By Priya PedamkarPriya Pedamkar

PostgreSQL ORDER BY Random

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;

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)
  • 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;

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 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:

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 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:

Limit Clause Example 3

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:

Specified Column Example 4

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:

PostgreSQL ORDER BY Random Example 5

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 –

  1. Introduction to PostgreSQL Timestamp
  2. How to Notify Works in PostgreSQL?
  3. PostgreSQL JSON (Examples)
  4. Introduction to PostgreSQL Timestamp
Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  7+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA

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

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

Let’s Get Started

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