Tags

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


SQL Puzzle | The TWIN MAX Puzzle | SQL Interview Question

In this puzzle you have to find records where you have maximum salary and maximum experience. Both the condition should be met to fetch the rows.

Please check the sample input and the expected output.

Sample Input

EmpId Nm Salary Experience
1 Pawan 5000 11
2 Avtaar 1000 10
3 Kishan 5000 9
4 Ishu 1000 7
5 Nika 4500 11
6 Vaibhav 5000 5

Expected Output

EmpId Nm Experience Salary
1 Pawan 11 5000

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

--

CREATE TABLE TheMaximumsPuzzle
(
	 EmpId INT
	,Nm VARCHAR(10)
	,Salary INT
	,Experience INT
)
GO

INSERT INTO TheMaximumsPuzzle VALUES
(1,'Pawan',5000,11),
(2,'Avtaar',1000,10),
(3,'Kishan',5000,9),
(4,'Ishu',1000,7),
(5,'Nika',4500,11),
(6,'Vaibhav',5000,5)
GO

SELECT * FROM TheMaximumsPuzzle
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

1 SOLUTION

SOLUTION – 1

--

SELECT k.EmpId,k.Nm,k.Experience,k.Salary
FROM
(
	SELECT * , MAX(experience) OVER() a , MAX(Salary) OVER() b
	FROM TheMaximumsPuzzle
)k WHERE Salary = b AND Experience = a


--

Output-1

--                            

EmpId       Nm         Experience  Salary
----------- ---------- ----------- -----------
1           Pawan      11          5000

(1 row affected)

--

SOLUTION – 2 | old School Solution

--

SELECT k.EmpId,k.Nm,k.Experience,k.Salary
FROM TheMaximumsPuzzle k
CROSS APPLY
(
	SELECT MAX(experience) a , MAX(Salary) b
	FROM TheMaximumsPuzzle
)z WHERE Salary = b AND Experience = a

--

Output-2

--                            

EmpId       Nm         Experience  Salary
----------- ---------- ----------- -----------
1           Pawan      11          5000

(1 row affected)


--

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

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

Advertisements