I recently came across one of new functions of SQL - ROW_NUMBER and PARTITION BY. So, how did i had encounter with these function. Let me tell you the scenario
ScenarioI had a Employee table containing the columns Employee Name, Total Salary credited and for which month salary was credited. I need to get the last paid salary of each employee. Employee table had all the salary records of the employee who ever worked with the company. This means that table had records of those employee also who had already left the company and who are presently working in the company.
Below is the table design:
Below is the data in the table
Below is the result we are expecting
So, what we have to do to get this result. SQL has function named as ROW_NUMBER() which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Below is the syntax:
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
Below is the query to get our expected result:
With summary as ( select Employee_Name,Salary,Sal_Month,ROW_NUMBER() over (Partition by Employee_Name order by Sal_month desc) as rk from [Employee_Salary] ) select * from summary where rk=1
I hope you would find many scenarios where you can use this function.
Get back to me in case of any queries / questions