EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL RANK()

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL RANK()

PostgreSQL RANK()

Introduction to PostgreSQL RANK()

The following article provides an outline on PostgreSQL RANK(). We can assign a rank to each row of the partition of a result set by using the RANK() function. The rank of the first row of a partition is 1. The rank is incremented in a fashion where the next row’s rank is equal to the number of rows tied to the rank. The rank calculation does not need any explicit parameter as its values are calculated internally using the OVER clause.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

RANK() OVER (
[PARTITION BY partition_exp, .... ] ORDER BY sort_exp [ASC | DESC], ....
)

Explanation:

  • PARTITION BY clause: The rows of the partitions of the result set to which the PostgreSQL RANK() function is applied.
  • ORDER BY clause: Defines the order of rows in each partition to which the PostgreSQL RANK() function is applied.

How RANK() Function works in PostgreSQL?

  • In order to calculate the rank of the next row, the PostgreSQL, the RANK function adds the number of rows to the rank assigned on the previous rank.
  • Because of this, the rank might not be sequential.
  • All of the rows of the partition with the same values get the same rank.

Examples of PostgreSQL RANK()

Given below are the examples:

Now let’s create a new table of name ‘Grades’, which will have a column named’Grade’ using CREATE TABLE statement as follows:

Code:

CREATE TABLE Grades (
Grade VARCHAR(1)
);

Now, insert some data into the ‘Grades’ table using the INSERT statement as follows:

Code:

INSERT INTO Grades(Grade)
VALUES
('A'),('A'),('A'),
('B'),('B'),
('C'),
('D');

Illustrate the content of the Grades table with the help of the following snapshot and SQL statement.

Code:

SELECT
Grade
FROM
Grades;

Output:

PostgreSQL RANK() 1

Now, with the help of the RANK() function, we can assign a rank to the row of the Grade column in the result set of Grades table as follows:

Code:

SELECT
Grade,
RANK () OVER (
ORDER BY Grade
) grade_rank
FROM
Grades;

Output:

PostgreSQL RANK() 2

Here from the above snapshot, we can see that the first three rows have the rank assigned as rank 1, which same for all rows whose value is ‘A’.

The rank of the fourth and fifth rows is assigned to rank 4 because of the PostgreSQL.

RANK() function calculated it as the previous rank summed with a total number of rows tied to that rank. Similarly, it assigns rank 6 and ranks 7 to sixth and seventh rows, respectively.

Now let’s create tables of name ‘transaction’ and ‘invoices’ in order to understand the RANK function with the PARTITION BY clause.

Let’s create tables using the CREATE TABLE statement as follows:

Code:

CREATE TABLE transaction (
transaction_id serial PRIMARY KEY,
transaction_data VARCHAR (256) NOT NULL
);

CREATE TABLE invoices (
invoice_id serial PRIMARY KEY,
transaction_id INT NOT NULL,
invoice_data VARCHAR (256) NOT NULL,
invoice_amount INT NOT NULL,
FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id)
);

Now insert some data in the transaction and invoices table by using the INSERT statement as follows:

Code:

INSERT INTO transaction (transaction_data)
VALUES
('Purchase of Mobile'),
('Purchase of PC'),
('Purchase of Headphone'),
('Purchase of Mouse'),
('Purchase of Cable');

INSERT INTO invoices (invoice_data, transaction_id,invoice_amount)
VALUES
('Purchase of Mobile', 1,30500),
('Purchase of Mobile', 1,30500),
('Purchase of Mobile', 1,20500),
('Purchase of PC', 2,15000),
('Purchase of PC', 2,12000);

Illustrate the result of the above statement with the help of the following snapshots and select statements:

Code:

select * from transaction;

Output:

PostgreSQL RANK() 3

Code:

select * from invoices;

Output:

PostgreSQL RANK() 4

Example #1

without PARTITION

This example assigns a rank to each row as per the transaction_id of invoices.

Code:

SELECT
invoice_id,
invoice_data,
invoice_amount,
RANK () OVER (
ORDER BY invoice_amount DESC
) invoice_amount_rank
FROM
invoices;

Output:

without PARTITION

In the above example, the entire table is considered as a single PARTITION as we have not defined the Partition BY clause.

Example #2

with PARTITION BY

Code:
SELECT
invoice_id,
invoice_data,
invoice_amount,
transaction_data,
RANK () OVER (
PARTITION BY i.transaction_id
ORDER BY invoice_amount DESC
) invoice_amount_rank
FROM
invoices i
INNER JOIN transaction t
ON i.transaction_id = t.transaction_id;

Output:

with PARTITION BY

In the above example, you can see we have defined PARTITION BY clause. The PARTITION BY clause splits the invoices into partitions which are grouped by the transaction_id column. Also, we have an ORDER BY clause defined, which sorts invoices high to low in each partition by their invoice_amount.

Here you can clearly visualize the invoices have assigned with rank using the RANK function for each transaction data, and it is again reinitialized when transaction data changed.

Conclusion

We hope from the above article you have understood how to use the PostgreSQL RANK function and how the PostgreSQL RANK() function works to assign the rank and fetch the data. Also, we have added some examples of the PostgreSQL RANK() function to understand it in details.

Recommended Articles

This is a guide to PostgreSQL RANK(). Here we discuss the introduction, how the rank() function works in PostgreSQL, along with respective examples. You may also have a look at the following articles to learn more –

  1. Wildcards in PostgreSQL
  2. PostgreSQL LEFT JOIN
  3. PostgreSQL FETCH
  4. Sequence in PostgreSQL
  5. PostgreSQL Boolean | How to Works?
  6. Guide to PostgreSQL COALESCE

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE 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
    • 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 Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • 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 Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • 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 cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • 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 JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • 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 WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • 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

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

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
Book Your One Instructor : One Learner Free Class

Let’s Get Started

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

EDUCBA

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

Forgot Password?

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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More