Postgresql Rank function

In this PostgreSQL tutorial, we will study the use of the Postgresql rank function and we are going to discuss the following list of topics.

• Postgresql rank
• Postgresql rank window function
• Postgresql rank vs row_number
• Postgresql rank in where clause
• Postgresql rank filter
• Postgresql rank dense
• Postgresql rank limit
• Postgresql rank count
• Postgresql rank vs dense_rank
• Postgresql rank group by

Postgresql rank

In this topic, we’re going to discover ways to use the PostgreSQL RANK() feature to assign a rank for each row of an end result set. Besides, rows with comparable values get a comparable rank. The below syntax describes the RANK() feature. Let’s check the syntax first.

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

In the above syntax, the PARTITION BY clause will flow into rows of the end result set into walls to which the RANK() feature is applied. Then, the ORDER BY clause specifies the order of rows in each partition to which the feature is adjusted.

The function is largely beneficial for growing top-N and bottom-N reports. Now we are able to recognize the instance PostgreSQL RANK() feature demo. Firstly, create a table position named function and we are able to insert rows into it. Let’s check the queries.

CREATE TABLE position (
	c VARCHAR(10)
);

INSERT INTO position(c)
VALUES('1st'),('2nd'),('3rd'),('2nd'),('4th'),('5th'),('6th'),('7th');

SELECT c FROM position;

Lastly, we will use the RANK( ) function to allocate ranks to the rows in the result set of the position table. Let’s check the final query.

SELECT c,RANK () OVER ( ORDER BY c) rank_number FROM position;

Also, we will check the output of the queries executed.

As we can see clearly from the output. The second and fourth rows receive the same rank because they have the same value of 2. The first, third, fifth, sixth, seventh, and eighth rows receive their respective ranks.

Also, check: Postgresql length of string

Postgresql rank window function

A window operating in Postgresql will perform a calculation across a group of table rows that are somehow involving this row. However, in contrast to regular aggregate functions, the utilization of a window function doesn’t cause rows to become sorted into one output row the rows retain their separate identities.

Here is an example that shows the way to compare every worker’ earnings with the typical salary within the employee table. Let’s check the code.

SELECT country, id, salary, rank() OVER (PARTITION BY address ORDER BY salary DESC) FROM employee;

If a query involves multiple window functions, it’s attainable to write down each one with a separate OVER clause, however maybe duplicative and erring if identical windowing behavior is needed for many operates. Instead, every windowing behavior can be named in a WINDOW clause and so documented in OVER. Let’ check the below syntax.

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM employee
  WINDOW w AS (PARTITION BY address ORDER BY salary DESC);

The window w is that the name of the window function. Some window functions don’t settle for any argument. The PARTITION BY clause divides rows into multiple teams or partitions so that the window function is applied. This clause is optional.

If we tend to skip the PARTITION BY clause, the window function can treat the entire result set as one partition. The ORDER BY clause explains the order of rows in every partition to that the window operate is applied.

This clause uses the NULLS FIRST or NULLS LAST choice to describe whether or not nullable values ought to be first or last within the result set. The default is NULLS LAST option. Let’ check the output of the queries implemented.

Read: PostgreSQL Delete Row

Postgresql rank vs row_number

PostgreSQL provides several ranking functions out of the box. Let’s check some differences and similarities between RANK() and ROW_NUMBER(). The RANK() function and the ROW NUMBER() function are extremely similar. The sole distinction is that identical rows are assigned the same rank.

Furthermore, we must keep in mind that if the function skipped a number during ranking (because of row similarity), that rank will be skipped as well. We have a table employee followed by applying both functions within it. Let’s check the query for the RANK() function.

SELECT name,salary,RANK () OVER ( ORDER BY salary) rank_number FROM employee;

The ROW_NUMBER Function is the easiest of all to learn because this function will just rank all selected rows in an ascending order anyhow the values that were selected. Let’s check the query for ROW_NUMBER.

SELECT name,salary,ROW_NUMBER() OVER(ORDER BY name) from employee;

Let’s check the output of the above two queries.

Read: PostgreSQL Rename Column

Postgresql rank in where clause

Now we will learn the rank function with the where clause in Postgresql. The below statement uses the DENSE_RANK  function with a CTE to return the most costlier item in each item group. The WHERE clause generally appears right after the FROM clause of the SELECT statement. 

For filtering the rows returned from the SELECT clause the WHERE clause will use the condition for it. We have two tables items and items_group. PostgreSQL CTE is known as common table expressions to simplify complex queries.

We can reference various PostgresqlSQL statements including SELECT, INSERT, UPDATE and DELETE because it is a mutual table expression. Let’s check the query below.

WITH cte AS(
	SELECT
		item_id,
		item_name,
		group_id,
		price,
		DENSE_RANK () OVER ( 
			PARTITION BY group_id
			ORDER BY price DESC
		) price_rank 
	FROM
		items
) 
SELECT 
	item_id, 
	item_name, 
	price
FROM 
	cte
WHERE 
	price_rank = 1;	

Let’s check the output of the above query.

Read: PostgreSQL Like With Examples

Postgresql rank dense

Now we are going to learn how to use the PostgreSQL DENSE_RANK() function to assign a rank to each row within a partition of a result set, with no gaps in ranking values. Basically, the dense rank will return the rank of rows within the partition of a result set, without any gaps in the ranking.

The DENSE_RANK() function consistently returns successive rank values. The DENSE_RANK() function will return a similar rank for the rows which have similar values for every partition. Below is the syntax for the DENSE_RANK() function.

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The DENSE_RANK() function is applied to every row in each partition defined by the PARTITION BY clause, in the sort order described by the ORDER BY clause. It will restart the rank when crossing the partition boundary.

The PARTITION BY clause is optional. If we skip it, the DENSE_RANK() function handles the complete result set as a single partition. Let’s understand this with an example. We will create the table dense_position first followed by inserting values in it. Then, we will query data from the dense_position table.

CREATE TABLE dense_position (
	c VARCHAR(10)
);

INSERT INTO dense_position(c)
VALUES('1st'),('2nd'),('3rd'),('2nd'),('3rd'),('4th'),('5th'),('6th');

SELECT c from dense_position;

Lastly,  we will use the DENSE_POSITION function to assign a rank to each row in the result set.

SELECT c, DENSE_RANK() OVER (ORDER BY c) dense_rank_number FROM dense_position;

Let’s check the output of the above queries.

Read: PostgreSQL Trim with Examples

Postgresql rank filter

In Postgresql, the FILTER clause supports managing subsets of data that meet unspecified conditions so we avoid aggregations. Now we will be using the FILTER Clause in PostgreSQL to Execute Functions. An advantage of the FILTER clause is that it’s more understood than the CASE when clause when used to aggregate queries.

Moreover, we’ll learn some examples of using FILTER to execute aggregate functions or stored procedures. We use some keywords in Postgresql with the FILTER Clause. Let’s check them below:

FROM — the FROM keyword in Postgresql is used to specify which table to query with the FILTER clause.

AS — this keyword is used in Postgresql to create an alias or correlated name, for a table or query results set.

ANY and ALL — these keywords are used in conjunction with WHERE to filter records meeting specific criteria in Postgresql

We always use the ‘COUNT()’ Function to filter the Number of Records that Match a Query. The COUNT() function will return only the number of records in the table that matches the specified filter. The below syntax is for COUNT() used in conjunction with the FILTER clause in PostgreSQL.

We can check that the AS keyword is used to assign a name to the column which contains the count for the results. Let’s check an example of a statement in psql using the COUNT keyword with the student table. If we need to filter or group rows we can use sub-select after the window calculations are performed. Let’s check the query below.

SELECT country, id, salary 
FROM
 ( SELECT country, id, salary,
rank() OVER (PARTITION BY address ORDER BY salary DESC, id) AS pos
FROM employee
) AS ss
WHERE pos < 3;

Let’s check the output of the above query.

Read: PostgreSQL Length + 12 Examples

Postgresql rank vs dense_rank

• dense_rank: This function assigns the rank for each group and returns the rank of rows in the partition of a result set, without any gaps in the rankings so the ranking is not skipped.
• rank: this function will assign a rank for each row of an end result set. Besides, rows with comparable values get a comparable rank.

Let’s check the queries for rank vs dense_rank.

SELECT c, DENSE_RANK() OVER (ORDER BY c) dense_rank_number FROM dense_position;

SELECT c,RANK () OVER ( ORDER BY c) rank_number FROM position;

Let’s check the output of the above queries.

Read: Postgresql now() function

Postgresql rank limit

In Postgresql, LIMIT fetches only a part of the rows which might be generated by the rest of the query. If a limit count is given, no greater than that many rows can be returned. When using LIMIT, we must include an ORDER BY clause to ensure that the result rows are in a logical order.

We have a table employee and we will be using the rank function with LIMIT. Let’s check the query for that.

SELECT ranks.rank
FROM (
  SELECT id, rank() OVER (ORDER BY salary DESC) AS rank
  FROM patrons
) ranks
WHERE id = 1
LIMIT 1;

Let’s check the output of the above code.

Read: Postgresql Average + Examples

Postgresql rank count

The COUNT() is a function in Postgresql that helps us to get the range of rows that matches a specific condition of a query. It is also an aggregate function. This function returns the total number of rows returned by a SELECT statement, including NULL and duplicates. Now we will apply the rank function with the count function in table billings. Let’s check the below query.

SELECT 
    customer_id,
    count,
    billing_id,
    billing_timestamp,
    dispatch_seconds,
    distance
FROM (
    SELECT 
        *, 
        count(*) over (partition by customer_id), 
        first_value(billing_id) over (partition by customer_id order by billing_timestamp) 
    FROM billing
)s

WHERE billing_id = first_value;

Let’s check the output of the above query.

Read: Postgresql unique constraint

Postgresql rank group by

In Postgresql, we use the GROUP BY clause with the SELECT statement to collaborate those rows in the table that have matching data. We have done this so that redundancy doesn’t occur in the output. Now we will be using the GROUP BY clause in the RANK function.

We have already created a table employee and let’s check the query for rank function with the group by clause.

SELECT RANK() OVER(ORDER BY country) ,country
FROM Employee Group By country ;

Let’s check the output of the above query.

You may also like to read the following PostgreSQL tutorials.

• PostgreSQL Subquery with Examples
• PostgreSQL Update Join + Examples
• Postgresql Add Foreign Key
• PostgreSQL group by with examples

In this tutorial, we have studied the use of the Postgresql rank function and we have discussed the following list of topics.

• Postgresql rank
• Postgresql rank window function
• Postgresql rank vs row_number
• Postgresql rank in where clause
• Postgresql rank filter
• Postgresql rank dense
• Postgresql rank limit
• Postgresql rank count
• Postgresql rank vs dense_rank
• Postgresql rank group by