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
Auf dieser Seite können Sie das Online-Video ROW_NUMBER() Analytic Function in SQL Server - #9 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Everyday Be Coding 24 Mai 2024 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 137 Mal angesehen und es wurde von 11 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
