RANK() Analytic Function in SQL Server
#SQLServer #RANKFunction #Analytics #DataAnalysis #SQLExamples #AnalyticFunctions #SQLTips #DatabaseDevelopment #SQLQueries #DataScience #SQLLearning #BusinessIntelligence #TechEducation #DataEngineering #ProgrammingTips #Technology #BigData #DatabaseManagement #TechCommunity #DataInsights
The RANK() analytic function in SQL Server is used to assign a rank to each row within a result set based on the specified ordering. Unlike the ROW_NUMBER() function, RANK() assigns the same rank to rows with equal values, and then leaves a gap in the sequence for subsequent rows.
Here's how it works:
Syntax:
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
PARTITION BY: Divides the result set into partitions to rank rows within each partition separately.
ORDER BY: Specifies the column or expression used to determine the order of rows within each partition.
Example:
Consider a table named "Employees" with columns "EmployeeID", "Name", and "Salary". We want to rank employees based on their salary within each department.
Below is the SQL script that creates the "Employees" table with the specified columns and inserts sample data. It then uses the RANK() analytic function to rank employees based on their salary within each department:
-- 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 rank employees based on salary within each department
SELECT
EmployeeID,
Name,
Department,
Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM
Employees;
Select SQL Query:
SELECT
EmployeeID,
Name,
Department,
Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM
Employees;
Output Result:
EmployeeID Name Department Salary Rank
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:
The RANK() function assigns a rank to each employee within their department based on their salary.
Employees with the highest salary in each department receive a rank of 1, followed by subsequent ranks for lower salaries.
If there are ties (i.e., multiple employees with the same salary), they will receive the same rank, and the next rank will skip the number of tied rows.
Summery:
This function is useful for scenarios where you need to identify the relative position of rows based on a specific criteria, such as ranking employees by salary, sales performance, or other metrics within different groups or categories.
Understanding the RANK() analytic function in SQL Server allows for efficient ranking and analysis of data within result sets, providing valuable insights into the relative standing of rows.
Chapter :
00:00 Introduction to RANK() Analytic Function
00:08 Function Syntax
00:52 Example: Create Scripts | Query | Output result
00:52 Summery
Thank you for watching this video
EVERYDAY BE CODING
Watch video RANK() Analytic Function in SQL Server online without registration, duration hours minute second in high quality. This video was added by user Everyday Be Coding 25 May 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 91 once and liked it 12 people.