Tags


SQL Puzzle | Highest and lowest value rows alternatively in SQL Server

Write a T-SQL query that fetches records where the person’s first and last call of the day is to the same person.

Please check out the sample input and the expected output.

Sample Inputs

Id Name Salary
1 A 7000.00
2 B 30000.00
3 C 6000.00
4 D 10000.00
5 E 800.00
6 F 500.00

Expected Output

Id Name Salary
1 B 30000.00
6 A 234.00
3 D 10000.00
5 F 500.00
100 A 7000.00
4 E 800.00
2 C 6000.00

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE #emp_records
(
 Id INT,
 Name VARCHAR(80),
 Salary NUMERIC(8, 2)
)
GO
 
INSERT INTO #emp_records
(Id, Name, Salary)
VALUES(1, 'A', 7000),
(2, 'B', 30000),
(3, 'C', 6000),
(4, 'D', 10000),
(5, 'E', 800),
(6, 'F', 500)

--

—

SOLUTION – 1

--

---

DECLARE @Count INT = 0
SELECT @Count = COUNT(1) FROM emp_records
DECLARE @limit INT = CEILING(@Count / 2.0)

;WITH CTE AS
(
	SELECT * FROM
	(
		SELECT * , ROW_NUMBER() OVER(ORDER BY Salary DESC) rnk FROM emp_records
	)B1 
	WHERE rnk <= @limit
)
,CTE1 AS 
(
	SELECT * FROM
	(
		SELECT *, ROW_NUMBER() OVER(ORDER BY Salary ASC) rnk FROM emp_records
	)B2
	WHERE rnk <= @limit - (@Count % 2.0) 
)
SELECT * FROM CTE c
UNION ALL
SELECT * FROM CTE1
ORDER BY rnk ASC, Salary DESC


--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com