Javatpoint Logo
Javatpoint Logo

SQL Windows Functions

A window function allows the user to perform computation across several table rows associated with the current row. It is similar to an aggregate function as it is also used for calculation. The only difference between a window function and any other regular aggregate function is that when implemented, the window function does not groups rows into a single output row. The identity of the rows on which the operation is performed remains different. The function can easily manage more than just the current row of the query result.

Let's consider an example to compute the running total using the window function. First, we will create a table to perform SQL Window Function. Here we will use the Student table and implement the operation.

The table is as follows:

S_ID S_Name S_Dept S_Country S_Age
100 Aman Science India 19
105 Harsh Maths England 20
110 Harshit Science India 17
200 Kavya Social Studies Canada 18
120 Abhay Maths India 21
102 Ravi English India 22

Implement the below queries to define and add data values to create the above table.


SQL Windows Functions

Implement the below SQL query to perform aggregate on the S_Age column of the table.


SQL Windows Functions

As you can see in the above illustration, the query to compute the aggregate is implemented without needing to use the GROUP BY clause.

Let us consider this example to learn the syntax for implementing the window function and understand its functioning.

Window Functions Syntax

Windows functions perform aggregate and rank operations over a specific window of rows. The functions are implemented with an OVER clause to define the records set. It performs two functions:

PARTITION BY Clause: It divides the rows into partitions. Partitions refer to a set of rows that meet specific criteria.

ORDER BY Clause: It arranges the rows within specific partitions into sequential order.

Note: If the user does not specify partitions in the OVER clause, then the ORDER BY clause is implemented for all the records in the table.

The Syntax to implement Windows Functions is as follows:

Here, the window_function refers to any aggregate or ranking function to be implemented by the user; column_name1 is the column to be selected column_name2 is the column on which the window function will be applied. column_name3 represents the column on the basis partition of the record is to be done. The new_column is the Name of the new column, and the table_name is the Name of the table that contains the data.

The SUM function is the first component to compute the aggregate using the Windows function, which is implemented similarly to any other aggregate function. When the OVER clause is used with the SUM function, it is designated as a window function.

The above query can be translated to return the sum of ages over the entire result order by the beginning of the record. The user can limit the window for which aggregate is being computed from the complete database to separate groups within the database by using the PARTITION BY clause. The query will be changed to as follow:


SQL Windows Functions

In the above query, the table is grouped based on the Student's Country (S_Country), and the order is done based on S_ID. The running total is performed on the S_Age column for all values greater than or equal to 20.

The above query groups the student based on their respective country. It means the students from different countries act as different tables. It means that the Sum function will compute the aggregate differently for all the partitions.

Note: Implementing the window function along with the standard aggregation in the same query is impossible. As the user cannot use Windows functions in the GROUP BY clause. Some examples of Windows functions are SUM, COUNT, and AVG.

Implementing Aggregate Functions

While implementing the window functions, the user can also implement the same aggregates that will be implemented by the user in any other scenario. These window functions are SUM, COUNT, and AVG.


SQL Windows Functions


ROW_NUMBER() is a predefined SQL function that assigns the sequential number to each row of records. It begins with 1, and the following number is assigned based on the ORDER BY clause used in the SQL statement. The user does not require to specify any parameter in the Row_Number() function.


SQL Windows Functions

Ranking Window Functions

The ranking windows functions include the following functions:




RANK(): The name function is implemented to assign a rank to all the records within every partition. Rank is assigned such that the first row in each partition is Rank 1. The next record is assigned the next consecutive number as its rank. If two rows have the same value, the same rank will be assigned to the record. The next rank will be skipped when two rows have the same rank.

DENSE_RANK(): It assigns a rank to every row in the partition. It works similarly to the rank function in that the first row in each partition will be assigned rank 1, and if the two rows have the same value, then they will have the same rank. The difference between the RANK() and DENSE_RANK() function is that if two rows have the same rank in DENSE_RANK, the next row will be assigned the next consecutive number as the next records rank. It means that no rank is skipped.

ROW_NUMBER(): It assigns a row number to each record in the table. Consecutive numbers are assigned to all the records in the table. Ranking begins with 1 in each table. If the user implements a partition, the first row will assign 1. No two rows in the same partition can have the same row number.

Note: The user needs to implement the ORDER BY clause when implementing the Rank Window Function in SQL.

For example,

Compute the row no., rank, and dense rank of students in the Student table according to S_Age.


SQL Windows Functions

Implementing Ranking Window Function With Partition By Clause


SQL Windows Functions
Next TopicCoalesce SQL

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA