Suppose we have an Employee table and each employee belongs to different department like Finance, HR, IT etc. Each department has many employees. We have to find the Department-wise maximum salary along with the employee name, employee code and other details.
-- Creating Employee Table tbl_employee
CREATE TABLE [dbo].[tbl_employee](
[emp_code] [int] PRIMARY KEY,
[emp_name] [nvarchar](50) NOT NULL,
[emp_sal] [int] NOT NULL,
[emp_dept] [nvarchar](50) NOT NULL
)
GO
-- Inserting data in tbl_employee
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (11, N'Simon', 1500, N'IT')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (12, N'Rahul', 2000, N'IT')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (13, N'John', 1800, N'Finance')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (14, N'Tina', 1500, N'HR')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (15, N'Sachin', 1600, N'HR')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (17, N'Peter', 1500, N'HR')
GO
INSERT [dbo].[tbl_employee] ([emp_code], [emp_name], [emp_sal], [emp_dept]) VALUES (18, N'Kumar', 1900, N'Finance')
GO
-- Select data from tbl_employee
SELECT * FROM [dbo].[tbl_employee]
-- SQL for department wise maximum
salary
SELECT emp_code, emp_name, emp_sal, emp_dept FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY emp_dept order by emp_sal DESC) AS RowNum
FROM [dbo].[tbl_employee]
) AS NewTbl WHERE NewTbl.RowNum = 1
