ROW_NUMBER() Analytic Function in SQL Server - #9
#SQLServer #ROWNUMBER #Analytics #DataAnalysis #SQLExamples #AnalyticFunctions #SQLTips #DatabaseDevelopment #SQLQueries #DataScience #SQLLearning #BusinessIntelligence #TechEducation #DataEngineering #ProgrammingTips #Technology #BigData #DatabaseManagement #TechCommunity #datainsights
The ROW_NUMBER() function in SQL Server is used to assign a unique sequential integer to each row within a partition of a result set. It's often used for pagination, ranking, or identifying duplicates.
Function Syntax:
SQL :
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression)
PARTITION BY: Specifies the column or columns used to partition the result set. ROW_NUMBER() restarts numbering for each distinct value in the partitioning column(s).
ORDER BY: Specifies the column or columns used to determine the order of rows within each partition. The ROW_NUMBER() function assigns numbers based on the specified order.
Example:
Let's consider a table named "Employees" with columns "EmployeeID", "Name", "Department", and "Salary". We want to assign a unique row number to each employee based on their salary, partitioned by department, and ordered by salary in descending order.
SQL Scripts:
-- Create Employees table
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10, 2)
);
-- Insert sample data into Employees table
INSERT INTO Employees (EmployeeID, Name, Department, Salary)
VALUES
(101, 'Alice', 'Sales', 60000),
(102, 'Bob', 'Sales', 55000),
(103, 'Charlie', 'Sales', 50000),
(104, 'David', 'Marketing', 62000),
(105, 'Emma', 'Marketing', 58000),
(106, 'Frank', 'Marketing', 55000),
(107, 'Grace', 'Accounting', 70000),
(108, 'Henry', 'Accounting', 65000),
(109, 'Ivan', 'Accounting', 63000);
-- Query to assign row numbers based on salary within each department
SELECT
EmployeeID,
Name,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM
Employees;
SQL:
SELECT EmployeeID, Name, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
FROM Employees;
Output Result:
| EmployeeID | Name | Department | Salary | RowNum |
|------------|----------|------------|--------|--------|
| 101 | Alice | Sales | 60000 | 1 |
| 102 | Bob | Sales | 55000 | 2 |
| 103 | Charlie | Sales | 50000 | 3 |
| 104 | David | Marketing | 62000 | 1 |
| 105 | Emma | Marketing | 58000 | 2 |
| 106 | Frank | Marketing | 55000 | 3 |
| 107 | Grace | Accounting | 70000 | 1 |
| 108 | Henry | Accounting | 65000 | 2 |
| 109 | Ivan | Accounting | 63000 | 3 |
Explanation:
For each department, employees are ordered by salary in descending order.
The ROW_NUMBER() function assigns a unique row number to each employee within their department based on the specified order of salaries.
The result set includes the original columns along with the newly assigned row numbers under the "RowNum" column.
This allows us to easily identify the rank of each employee within their respective department based on salary.
Chapter :
00:00 Introduction to ROW_NUMBER() Analytic Function
00:08 Function Syntax
00:52 Example: Create Scripts | Query | Output result
Thank you for watching this video
EVERYDAY EB CODING
Watch video ROW_NUMBER() Analytic Function in SQL Server - #9 online without registration, duration hours minute second in high quality. This video was added by user Everyday Be Coding 24 May 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 103 once and liked it 11 people.