Menu Close

MySQL Window Functions With Examples

mysql window functions

A must-know for Data Scientists, Data Engineers, and Data analysts.

Hi folks, I hope you are doing well. These MySQL Window Functions are going to be very important and useful because in this tutorial you will learn everything about MySQL window functions with the help of the proper examples as well as the explanation so that you don’t have any confusion regarding window functions in MySQL.

Window functions in MySQL come under the advanced topics which are most used by data scientists and data analysts to perform large calculations on a set of rows. Throughout this article, I will explain all about MySQL window functions with the help of real-life examples so that you can understand window functions easily.

What are MySQL Window Functions

Window function in MySQL comes under the advanced concept in MySQL. Window functions were first introduced in MySQL version 8.0 and MySQL window functions are used to perform operations across a set of table rows that are related to the current row. Here the meaning of the current row is to that row for which function evaluation occurs. The calculation of the window function is the same as the calculation done by using the aggregate function used with the GROUP BY clause.

The only difference between the windows functions and aggregate functions with GROUP BY clause is that GROUP BY clause applies aggregates function on the rows that have identical values and returns only a single value same as the windows function performs a calculation on a set of rows that have identical values but it retains all the rows and their identity and return value for each of the row.

Here I have shared a good visualization of the Windows function and GROUP BY clause.

Window Functions vs Group By
Window Functions vs Group By

Now, we have to notice in the above image, we have a total of two Figures, Figure 1 represents the aggregate function, and Figure 2 represents the window function.

In Figure 1, How GROUP BY clause grouped rows with the same values ( Here same values representing the same color) into a single value, and in Figure 2, the windows function performs calculations on a set of rows that have identical values same as in Figure 1 but window functions retain all the rows as well along with a calculated value.

I hope you will have understood the meaning of MySQL window functions. If not. Wait just continue after this article, and you will understand.

Let’s take an example to understand the difference between a window function and an aggregate function because an initial understanding of these two concepts is most important.

If you understand the difference between these two concepts aggregate functions and Window functions, Then way ahead will be easier.

I have created an employee table having some records and I want to create a new column average_salary which will be the average salary of employees for each department.

Please see carefully!

Below are the records of the employee table:

+----+------------+-----------+-----------+---------------------+
| id | first_name | last_name | salary    | department          |
+----+------------+-----------+-----------+---------------------+
|  1 | Vishvajit  | Rao       |  25000.00 | IT Department       |
|  2 | Vinay      | Kumar     |  35000.00 | IT Department       |
|  3 | Minakshi   | Kumary    |  30000.00 | Digital Marketing   |
|  4 | John       | Doe       |  80000.00 | Data Department     |
|  5 | Akash      | Saini     |  25500.00 | Data Department     |
|  6 | Harshita   | Singh     |  20000.00 | Account and Finance |
|  7 | Ayush      | Shukla    |  23500.00 | IT Department       |
|  8 | Abhishek   | Mishra    | 120000.00 | Data Department     |
|  9 | Vaishanvi  | Mehta     |  75250.00 | Digital Marketing   |
| 10 | Mahima     | Chaudhari |  40300.00 | Data Department     |
| 11 | Arushi     | Shulka    |  46000.00 | Data Department     |
| 12 | Mahesh     | Kumar     |  90500.00 | IT Department       |
| 13 | Ajay       | Gupta     |  85340.00 | IT Department       |
| 14 | Manish     | Das       |  90000.00 | HR                  |
| 15 | David      | Russel    | 105000.00 | Data Department     |
| 16 | Nishant    | Chahal    |  65000.00 | Account and Finance |
| 17 | Darshan    | Natrajan  |  40000.00 | HR                  |
+----+------------+-----------+-----------+---------------------+

Firstly, I have used the MySQL GROUP BY clause to calculate the average salary of all the employees within a department.

After applying the GROUP BY Clause on the department column, The result will be this.

+---------------------+----------------+
| department          | average_salary |
+---------------------+----------------+
| IT Department       |   51868.000000 |
| Digital Marketing   |   52625.000000 |
| Data Department     |   69466.666667 |
| Account and Finance |   42500.000000 |
| HR                  |   65000.000000 |
+---------------------+----------------+

Now, I will use the window function concept to calculate the average salary of each employee within a department, let’s see what happens.

After applying the MySQL window function, The result will be this.

+---------------------+----------------+
| department          | average_salary |
+---------------------+----------------+
| Account and Finance |   42500.000000 |
| Account and Finance |   42500.000000 |
| Data Department     |   69466.666667 |
| Data Department     |   69466.666667 |
| Data Department     |   69466.666667 |
| Data Department     |   69466.666667 |
| Data Department     |   69466.666667 |
| Data Department     |   69466.666667 |
| Digital Marketing   |   52625.000000 |
| Digital Marketing   |   52625.000000 |
| HR                  |   65000.000000 |
| HR                  |   65000.000000 |
| IT Department       |   51868.000000 |
| IT Department       |   51868.000000 |
| IT Department       |   51868.000000 |
| IT Department       |   51868.000000 |
| IT Department       |   51868.000000 |
+---------------------+----------------+

In the First table, data is returned by the GROUP BY clause, and in the second table, data is returned by the window function. Basically, The GROUP BY clause compresses the rows into just three rows but window functions retained all 17 rows along with new columns averag_salary which is the average salary of all the employees within a department.

I hope you will have the meaning of the window function and GROUP BY clause. Let’s understand why we need to learn window functions.

Why should we need to use the window function in MySQL?

The main advantage of learning the window functions is, that it does not collapse rows together. It just retained all the rows along with new columns. Using window functions we can perform aggregate functions and not aggregate functions on the records at the same time. As you can see in the above two records table. In the First table, all 17 records collapsed but in the second table, it did not.

Window functions increase the efficiency and complexity of the SQL queries that analyze the data in the form of partitions or windows.

MySQL window function syntax

Here, I have shared the MySQL window function syntax.

SELECT column1, column2, window_function(expression) 
OVER(PARTITION BY <partition_list> ORDER BY <order_list> ROWS frame_clause)
FROM table_name;

Explanation of the above MySQL window() function syntax:

  • window_function represents the name of the window functions which we want to use for example AVG(), SUM(), MAX(), MIN(), etc are the window functions.
  • The expression indicates the name of the column name on which the window will operate. You can use more than one column as an expression.
  • OVER is nothing but it signifies that this is a window function.
  • PARTITION BY divides the rows into partitions so that we can specify which row to use to compute the window function.
  • partition_list is the name of the columns we want partitioned by.
  • ORDER BY is used to order the rows within each partition. This is optional.
  • order_list specifies the name of the columns we want to order by.
  • ROWS is used when we want to limit the further rows within our partition.
  • frame_clause defines how much we want to offset from the current row.

Types of MySQL window functions

MySQL Window Functions divided the Window functions into three groups such as aggregate, ranking, and value. Don’t worry, We will see all these categories and their functions one by one through example. You can see all three window function categories and all the window functions within each category.

Types of window functions
Types of window functions

Let’s see the meaning of all the above categories like aggregate, ranking, and value window functions.

aggregate window function

The aggregate window function is used to calculate the various aggregations and sets of rows like average, the sum of related rows, minimum values, and maximum value within each window or partition.

Ranking window function

Ranking functions are used to provide the ranking within each partition or window.

Value window function

This function allows us to compare values from previous values or rows within the partitions, or the first row or last row within the partition or window.

Data for applying the MySQL Window Functions

To apply all the above window functions, I have created a table just for demonstrations in MySQL called employee having some records. As You can see below.

Throughout this tutorial guide, we will use employee table and their data to apply all the Window functions. This tutorial is going to be very interesting and informative.

MySQL Window Functions
Data for applying MySQL Window functions

We will explore all the categories and their function one by one. I hope you have remembered the MySQL window function syntax which we have seen earlier.

First of all, we will go with aggregate functions.

MySQL Aggregate Window Functions

As we know aggregate functions are used to perform some calculations on the set of rows like total sum, maximum value, minimum value, and average value within a partition or window.

There are various types of aggregate functions available as you can see in the below list.

  • AVG()
  • SUM()
  • MIN()
  • MAX()
  • COUNT()

AVG() window function

The AVG() function is used to calculate the average value of the numeric column or the average value of the numeric column within a partition or window.

Here, I am going to calculate the average salary column for all employees within each department.

Example

SELECT 
	first_name, 
	last_name, 
	age, 
	salary, 
	designation, 
	department, 
AVG(salary) OVER (PARTITION BY department) as "average" 
FROM employee;
MySQL Window Functions
Using MySQL AVG() window function

Explanation of the above code:

Let’s match the above SQL code with the Window function syntax which we have seen above.

  • The columns first_name, last_name, age, salary, designation, and department are the columns that we want to select.
  • The second, AVG() function is the window function which takes a number column that is salary. Here salary indicates the expression in window_function() in the above syntax, that’s which is an expression that we want to use to calculate the average.
  • OVER is used to signify that this function with treated as a window function.
  • As we know the PARTITION BY clause can take one or multiple expressions that represent the column names, I have used the department column in PARTITION BY because I want to calculate the average employee salary department-wise.
  • Used as a keyword to provide the name of the new column.
  • The employee is representing the name of the table.

MySQL SUM() Window Function

Similarly, We can calculate the sum of all the employee salaries within a department. We will have to just replace the AVG() window function with the SUM() window function.

SELECT 
	first_name, 
	salary, 
	designation, 
	department, 
SUM(salary) OVER (PARTITION BY department) AS "sum_of_salary" 
FROM employee;
MySQL Window Functions
Using MySQL SUM() window function

In the above image, I have separated all the windows or partitions with different-different colors.

MySQL MAX() Window Function

Same as We can find the maximum salary of an employee within a department. To find the maximum salary, we will have to use the MAX() function.

SELECT 
	first_name, 
	salary, 
	designation, 
	department, 
MAX(salary) OVER (PARTITION BY department) AS "maximum salary" 
FROM employee;
MySQL Window Functions
Using MySQL MAX() window function

MySQL MIN() Function

Similarly, we can find the lowest salary of an employee within a department using the MIN() function.

SELECT 
	first_name, 
	salary, 
	designation, 
	department, 
MIN(salary) OVER (PARTITION BY department) AS "minimum salary" 
FROM employee;

MySQL COUNT() Window Function

The COUNT() window function in SQL is used to find the total number of records within a window or partition. Here, I am going to find the total number of employees exists on each department.

SELECT 
	first_name, 
	salary, 
	designation, 
	department, 
COUNT(*) OVER (PARTITION BY department) AS "total employees" 
FROM employee;
MySQL Window Functions
Using MySQL COUNT() Window function

MySQL Ranking Window function

The ranking is another category of the window function in MySQL. The ranking window function is used to provide the ranking of the records within each window or partition. You have remembered one thing about ranking functions, You don’t need to pass parameters in ranking functions like aggregate functions.

There are various types of Ranking functions available as follows.

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • PERCENT_RANK()
  • NTILE()

MySQL ROW_NUMBER() window function

ROW_NUMBER() function is a window function that comes under the ranking functions, It does not take any expression as a parameter and it is used to provide the rank of records within a partition or window.

Suppose we want to provide a ranking of each employee in the employee table based on their salary within a department. For example, the Rank of the highest salaried employee within an organization is 1, and so on.

Let’s see how can we do that.

SELECT 
	first_name, 
	salary, 
	designation, 
	department, 
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" 
FROM employee;
MySQL Window Functions
Using MySQL ROW_NUMBER() Window Function

As you can see, we have successfully applied the ROW_NUMBER() function to assign the rank of each employee within a department. Ranking will always start from 1 to a number of records in each window. As you can see in each department how does ranking get assigned? So this is how you can use MySQL ROW_NUMBER() window function.

MySQL RANK() window function

The MySQL RANK() function works the almost same as the ROW_NUMBER() function. The only difference between the ROW_NUMBER() and RANK() window functions is ROW_NUMBER() function will assign different ranks even if the multiple values have the exact value but the RANK() function will assign the same rank if the multiple values have the same or exact value. Maybe you will be confused here. All the stuff will clear when we will see an example.

Let me change the salary of some employees within a department with the same value. After changing the record data will look like this.

mysql> SELECT id, first_name, salary, designation, department FROM employee;
+----+------------+-----------+--------------------------+---------------------+
| id | first_name | salary    | designation              | department          |
+----+------------+-----------+--------------------------+---------------------+
|  1 | Vishvajit  |  25000.00 | Python Developer         | IT Department       |
|  2 | Vinay      |  35000.00 | Angular Developer        | IT Department       |
|  3 | Minakshi   |  30000.00 | SEO Specialist           | Digital Marketing   |
|  4 | John       |  80000.00 | Data Engineer            | Data Department     |
|  5 | Akash      |  25500.00 | Junior Data Engineer     | Data Department     |
|  6 | Harshita   |  20000.00 | Junior Accountant        | Account and Finance |
|  7 | Ayush      |  23500.00 | Angular Developer        | IT Department       |
|  8 | Abhishek   | 120000.00 | Senior Data Engineer     | Data Department     |
|  9 | Vaishanvi  |  75250.00 | Senior SEO Specialist    | Digital Marketing   |
| 10 | Mahima     |  40300.00 | Junior Data Scientist    | Data Department     |
| 11 | Arushi     |  40300.00 | Junior Data Analyst      | Data Department     |
| 12 | Mahesh     |  85340.00 | Senior Python Developer  | IT Department       |
| 13 | Ajay       |  85340.00 | Senior Angular Developer | IT Department       |
| 14 | Manish     |  90000.00 | Senior HR Manager        | HR                  |
| 15 | David      | 105000.00 | Senior Data Analyst      | Data Department     |
| 16 | Nishant    |  65000.00 | Senior Accountant        | Account and Finance |
| 17 | Darshan    |  40000.00 | Hiring HR                | HR                  |
+----+------------+-----------+--------------------------+---------------------+
17 rows in set (0.00 sec)

Let’s use the RANK() function.

SELECT 
	id,
	first_name, 
	salary, 
	designation, 
	department, 
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" 
FROM employee;
MySQL Window Functions
Using MySQL RANK() Window Function

As you can see in the above result set, The salary of an employee is the same whose ID is equal to 12 and 13, In this case, the RANK() window function assigned the same rank 1 and the same thing happened with employees whose ID is 10 and 11.

Please, Notice that for rows 6 and 7 the salary is 40300.00, therefore, both rows have been assigned the same rank 2 by RANK(), and rows 13 and 14 also have the same salary of 85340.00 That’s why both rows have assigned the same ranking 1 by RANK() function.

One most important things about the RANK() function is that it skips a ranking number if the multiple values have exact values. For example, you can see in rows 13 and 14, that rank 1 has been assigned but 2 has been skipped and started from 3 and the same thing happened in rows 6 and 7, The ranking assigned 4 and 5 have been skipped.

Well, Most of the time we don’t want to skip the ranking function, In that case, we can go with DESNE_RANK() window function.

MySQL DESNE_RANK() function

MySQL DESNE_RANK() window function works almost the same as the RANK() function, It does not skip the ranking number like the above example. Here we will go with the same example that we have seen above.

SELECT 
	id,
	first_name, 
	salary, 
	designation, 
	department, 
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" 
FROM employee;
MySQL Window Functions
Using MySQL DENSE_RANK() Window Function

As you can see in the above result, the DENSE_RANK() function did not skip the ranking number like the RANK() window function.

MySQL PERCENT_RANK() Window Function

PERCENT_RANK() function is also part of the Ranking window function that is used to assign the percent rank to each of the rows within a window or partition but quite different from other ranking functions that we have seen above.

Let’s see how it is diffrent ?

In PERCENT_RANK() window function:

  • The lowest rank always starts from 0.
  • The last rank always ends with 1.
  • The rank of each row is evaluated by the ( current_row_number -1 ) / total_number_of_rows -1 ) formula. Where current_row_number represents the row number of itself and total_number_of_rows indicates the number of records that exist in the table.

Let’s demonstrate PERCENT_RANK() through an example.

SELECT 
	id,
	first_name, 
	salary, 
	designation, 
	department, 
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS "rank" 
FROM employee;
MySQL PERCENT_RANK() Window Function
Using MySQL PERCENT_RANK() Window Function

As you can in the above result set, the How PERCENT_RANK() window function is implemented, and within a window or partition, each rank is started from 0 and ends at 1. Each rank is calculated from the ( current_row_number -1 ) / ( number of rows in window or partition -1 ) formula even to verify the rank, you can calculate it yourself. let’s try to calculate the rank of the IT Department.

In the IT Department, There are 5 employees and their rows numbers are 1, 2, 3, 4, 5. and the total number of records in the employee table is 17

Formula:- ( current_row_number -1 ) / ( number of rows in window or partition -1 ).

Rank for row 1:

  • rank = (1 – 1)/(5 – 1 ) = 0

Rank for row 2:

  • rank = (2 – 1)/(5 – 1 ) = 0

So this is how a PERCENT_RANK rank is calculated for a row.

MySQL NTILE() Window Function

NTILE() is the window function used to distribute the rows of order partitions in a pre-defined number of groups.

It takes a number value as a parameter which represents the number of groups to be created. The NTILE() function divides the total number of rows within a partition by the number of groups specified in the NTILE() function.

  • If the number of rows is divisible by a number of groups then each partition will have the same number of rows.
  • If the number of rows is not divisible by a number of groups Then each partition will have different rows and large rows always be ahead of a smaller number of rows.
SELECT 
	first_name, 
	designation, 
	department, 
    salary, 
	NTILE(3) OVER (PARTITION BY department order by salary DESC) AS "Ntile" 
FROM employee;
MySQL NTILE() Window Function

MySQL Value Window Function

In MySQL, The value window functions are used to compare the values. There is various type of value window function available in MySQL. All those functions are listed below. All the value window functions have great value in extracting power from other rows.
Value window functions are mostly used to create reports. All the values window functions are listed below.

  • LAG()
  • LEAD()
  • FIRST_VALUE()
  • LAST_VALUE()
  • NTH_VALUE()

MySQL LAG Window Function

The LAG window function is a kind of value window function that has the power to extract value from previous rows. It is the best value function in order to compare the value of the current row to previous records.

The LAG() window function takes three parameters.

  • column_name:- Required, This argument indicates the column name on which you want to perform the calculation.
  • offset:- Optional, Number of rows back from the current row which to access the data. By default, it is 1.
  • default:- Optional, default value to be returned if the offset goes beyond the scope of the partition.

Let’s understand this by an example.

Now this time I am not going to use the PARTITION BY clause. When we will not use PARTITION BY clause it will treat whole records as a single window or partition.

Let’s see.

mysql> SELECT
    ->   first_name,
    ->      department,
    ->   salary,
    ->    LAG(salary) OVER (ORDER BY salary DESC) AS "lag"
    ->    FROM employee;
+------------+---------------------+-----------+-----------+
| first_name | department          | salary    | lag       |
+------------+---------------------+-----------+-----------+
| Abhishek   | Data Department     | 120000.00 |      NULL |
| David      | Data Department     | 105000.00 | 120000.00 |
| Manish     | HR                  |  90000.00 | 105000.00 |
| Mahesh     | IT Department       |  85340.00 |  90000.00 |
| Ajay       | IT Department       |  85340.00 |  85340.00 |
| John       | Data Department     |  80000.00 |  85340.00 |
| Vaishanvi  | Digital Marketing   |  75250.00 |  80000.00 |
| Nishant    | Account and Finance |  65000.00 |  75250.00 |
| Mahima     | Data Department     |  40300.00 |  65000.00 |
| Arushi     | Data Department     |  40300.00 |  40300.00 |
| Darshan    | HR                  |  40000.00 |  40300.00 |
| Vinay      | IT Department       |  35000.00 |  40000.00 |
| Minakshi   | Digital Marketing   |  30000.00 |  35000.00 |
| Akash      | Data Department     |  25500.00 |  30000.00 |
| Vishvajit  | IT Department       |  25000.00 |  25500.00 |
| Ayush      | IT Department       |  23500.00 |  25000.00 |
| Harshita   | Account and Finance |  20000.00 |  23500.00 |
+------------+---------------------+-----------+-----------+
17 rows in set (0.00 sec)

In the above result set, As you can see. The lag column contained the salary of their previous employee salary. This could be very useful in order to compare current employee salaries with their previous ones to get the differences between salaries.
Notice that in the first row, The lag value is NULL because there is no previous record for the first row, That’s why it is happening.

To avoid the NULL value in the first row, You can pass an additional two parameters to the LAG() function. As you can see below example.

mysql> SELECT
    ->   first_name,
    ->      department,
    ->   salary,
    ->    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS "lag"
    ->    FROM employee;
+------------+---------------------+-----------+-----------+
| first_name | department          | salary    | lag       |
+------------+---------------------+-----------+-----------+
| Abhishek   | Data Department     | 120000.00 |      0.00 |
| David      | Data Department     | 105000.00 | 120000.00 |
| Manish     | HR                  |  90000.00 | 105000.00 |
| Mahesh     | IT Department       |  85340.00 |  90000.00 |
| Ajay       | IT Department       |  85340.00 |  85340.00 |
| John       | Data Department     |  80000.00 |  85340.00 |
| Vaishanvi  | Digital Marketing   |  75250.00 |  80000.00 |
| Nishant    | Account and Finance |  65000.00 |  75250.00 |
| Mahima     | Data Department     |  40300.00 |  65000.00 |
| Arushi     | Data Department     |  40300.00 |  40300.00 |
| Darshan    | HR                  |  40000.00 |  40300.00 |
| Vinay      | IT Department       |  35000.00 |  40000.00 |
| Minakshi   | Digital Marketing   |  30000.00 |  35000.00 |
| Akash      | Data Department     |  25500.00 |  30000.00 |
| Vishvajit  | IT Department       |  25000.00 |  25500.00 |
| Ayush      | IT Department       |  23500.00 |  25000.00 |
| Harshita   | Account and Finance |  20000.00 |  23500.00 |
+------------+---------------------+-----------+-----------+
17 rows in set (0.00 sec)

Note:- You can also use the PARTITION BY clause to divide the rows into disparate partitions.

MySQL LEAD() window function

The MySQL LEAD() window function works the same as the LAG() function, Unlike the LAG() function, The LEAD() function uses the value of the next row.

It accepts a total of three parameters.

  • column_name:- Required, This argument indicates the column name on which you want to perform the calculation.
  • offset:- Optional, Number of rows next to the current row which to access the data. By default, it is 1.
  • default:- Optional, default value to be returned if the offset goes beyond the scope of the partition.
SELECT 
	id,
	first_name, 
	designation, 
	department, 
    salary, 
LEAD(salary, 1, 0) OVER (ORDER BY salary DESC) AS "lead" 
FROM employee;
MySQL Window Functions
Using MySQL LEAD() Window Function

Note:- You can also use the PARTITION BY clause to divide the rows into disparate partitions.

MySQL FIRST_VALUE() Window Function

The FIRST_VALUE() window function returns the first value of a specified column for every row within a partition or window.

let’s see through an example.

Using FIRST_VALUE() without PARTITION BY clause:

If we do not define the PARTITION BY clause, then it will treat whole rows as a single window or partition. As you can see below screenshot.

SELECT 
	first_name, 
	designation, 
	department, 
    salary, 
FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS "FirstValue" 
FROM employee;
MySQL Window Functions
Using MySQL FIRST_VALUE() Window Function

Using FIRST_VALUE() with PARTITION BY clause:

If we do not define the PARTITION BY clause, then it will treat whole rows as a single window or partition. As you can see below screenshot.

SELECT 
	first_name, 
	designation, 
	department, 
    salary, 
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS "FirstValue" 
FROM employee;
MySQL Window Functions
Using MySQL FIRST_VALUE() Window Function with partition

MySQL LAST_VALUE() Window Function

The LAST_VALUE() window function works the same as FIRST_VALUE() function. Instead of getting the first value of the specified column, it uses the last value of the specified column.

SELECT 
	first_name, 
	designation, 
	department, 
    salary, 
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS "LastValue" 
FROM employee;
MySQL Window Functions
Using MySQL LAST_VALUE() Window Function

MySQL NTH_VALUE() Window Function

The NTH_VALUE() also work same as FIRST_VALUE() and LAST_VALUE() window function.Instead of getting value from the first row and last row, it takes value from the Nth row where N is the row number in a set of order rows.

It takes two parameters, the first is an expression that indicates the column name is the same as previous functions and the second is the row number to get the value.

Example:

In this example, I have created a column called NthValue which contains the name of the employee of the 3rd row whose salary is the 3rd highest salary in the result set because I have ordered the result set based on the salary.

MySQL Window Functions
Using MySQL NTH_VALUE() Window Function
 👉MySQL Window Functions Reference:- Click Here

Related Articles:-


Summary

So, In this amazing guide, We have seen everything about MySQL window functions with examples. Window functions in MySQL or any other database engine like SQL Server, PostgreSQL, etc are one of the most important concepts, especially for Data Engineers, Data analysts, and Data Scientists.

As Data Engineers, Data analysts, and Data Scientists you must have knowledge of MySQL window functions. The main advantage of using the Window function in MySQL is that it does not collapse the result set together like the GROUP BY clause, It just retains all the rows with their unique id and returns a single value for every row in the result set. You can use any window function as per your requirement.

I hope you found this article, Please keep visiting and share this kind of amazing tutorial.

Thanks for your valuable time…

Have a nice day.

How to Get Quarter from Date in MySQL
MySQL DROP COLUMN

Related Posts