Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Longest Emp Name in each department | Single SELECT | SQL Interview Question

In this puzzle you have to find employees with longest name in each department. Can you do that in a single select?. Please check the sample input and the expected output.

Notes –
1. The limitation is that we can only use a single SELECT for the puzzle.

Sample Input

Employees Table

EmpId EmpName DeptId
1 Pawan 1
2 Krishan Kant 1
3 Avtaar Kishan 2
4 Sharlee Diwan 3
5 Vaibhav 3
6 Inu 4
7 Ishu Madaan 5
8 Amreek 5

Department Table

DeptId DeptName
1 IT
2 Finance
3 Hr
4 Administration
5 Top Brass

Expected Output

DeptName LongestEmpName
Administration Inu
Finance Avtaar Kishan
Hr Sharlee Diwan
IT Krishan Kant
Top Brass Ishu Madaan

Use below script to create table and insert sample data into it.

--

CREATE TABLE Employees
(
	 EmpId INT
	,EmpName VARCHAR(100)
	,DeptId INT
)
GO

INSERT INTO Employees VALUES
(1,'Pawan',1),
(2,'Krishan Kant',1),
(3,'Avtaar Kishan',2),
(4,'Sharlee Diwan',3),
(5,'Vaibhav',3),
(6,'Inu',4),
(7,'Ishu Madaan',5),
(8,'Amreek',5)
GO

CREATE TABLE FindDepartmentWithLargestName
(
	 DeptId INT
	,DeptName VARCHAR(500)
)
GO

INSERT INTO FindDepartmentWithLargestName VALUES
(1,'IT'),
(2,'Finance'),
(3,'Hr'),
(4,'Administration'),
(5,'Top Brass')
GO

SELECT * FROM Employees
GO

SELECT * FROM FindDepartmentWithLargestName
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Single SELECT

--



SELECT 
	 DISTINCT L.DeptName 
	, FIRST_Value(EmpName) OVER(PARTITION BY E.DeptId ORDER BY LEN(EmpName) DESC) LongestEmpName 
FROM FindDepartmentWithLargestName L
INNER JOIN Employees E ON E.DeptId = L.DeptId
ORDER BY L.DeptName

--

Output-1

--                            

DeptName                 LongestEmpName
------------------------ ----------------------
Administration           Inu
Finance                  Avtaar Kishan
Hr                       Sharlee Diwan
IT                       Krishan Kant
Top Brass                Ishu Madaan

(5 rows affected)


--

SOLUTION – 2 | 2 SELECTs

--



SELECT DeptName , a.EmpName FROM 
(
	SELECT L.DeptName , E.EmpName , ROW_NUMBER() OVER(PARTITION BY E.DeptId ORDER BY LEN(EmpName) DESC) rnk
	FROM FindDepartmentWithLargestName L
	INNER JOIN Employees E ON E.DeptId = L.DeptId
)a WHERE rnk = 1
ORDER BY DeptName
--

Output-2

--                            

DeptName                 LongestEmpName
------------------------ ----------------------
Administration           Inu
Finance                  Avtaar Kishan
Hr                       Sharlee Diwan
IT                       Krishan Kant
Top Brass                Ishu Madaan

(5 rows affected)


--

SOLUTION – 3 | 2 SELECTs

--



SELECT DeptName , r.EmpName 
FROM FindDepartmentWithLargestName L
CROSS APPLY
(
	SELECT TOP 1 E.EmpName EmpName
	FROM Employees E 
	WHERE L.DeptId = E.DeptId
	ORDER BY LEN(EmpName) DESC
)r
ORDER BY DeptName

--

Output-3

--                            

DeptName                 LongestEmpName
------------------------ ----------------------
Administration           Inu
Finance                  Avtaar Kishan
Hr                       Sharlee Diwan
IT                       Krishan Kant
Top Brass                Ishu Madaan

(5 rows affected)


--

SOLUTION – 4 | 2 SELECTs

--



SELECT L.DeptName,E.EmpName
FROM Employees E
INNER JOIN
(
	SELECT E.DeptId , MAX(LEN(E.EmpName)) MLen
	FROM Employees E
 	GROUP BY E.DeptId
)r ON r.MLen = LEN(E.EmpName) AND E.DeptId = r.DeptId
INNER JOIN FindDepartmentWithLargestName L ON L.DeptId = E.DeptId
ORDER BY L.DeptName


--

Output-4

--                            

DeptName                 LongestEmpName
------------------------ ----------------------
Administration           Inu
Finance                  Avtaar Kishan
Hr                       Sharlee Diwan
IT                       Krishan Kant
Top Brass                Ishu Madaan

(5 rows affected)


--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com