SQL Puzzle | The Middle 4 Puzzle

Puzzle Statement

• The puzzle says you have to get middle 4 rows from the table using set based approach.
• If the number of rows in the table is less than or equal to 4, then return all rows. 
• If the number of rows are odd. E.g. 5 then you have get 4 rows, in this case you can either select first 4 rows or last 4 rows. I am taking last 4 rows.
• If the number of rows are even and more than 4, for e.g. 6 rows then we have to select exact 4 rows by eliminating 1st and last row.

Sample Input – 1

Expected output – 1

Sample Input – 2

Expected output – 2

Sample Input – 3

Expected output – 3

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

Script
Use below script to create table and insert some sample data-

--

CREATE TABLE FourMiddleRecords
(
    Value INT
)
GO

INSERT INTO dbo.FourMiddleRecords VALUES
(1),
(3),
(7),
(17),
(23),
(42),
(20),
(23),
(21),
(31),
(45),
(39)
--

SOLUTION #

--

DECLARE @ AS INT = 0 , @Records AS INT = 4-1
SELECT @ = COUNT(*) FROM FourMiddleRecords

SELECT Value Middle4Records FROM dbo.FourMiddleRecords
ORDER BY value
OFFSET  CASE 
		WHEN @<=4 THEN 0 
                WHEN @>4 THEN (@ - 1) / 2 - 1
	END ROWS
FETCH NEXT @Records + (1 - (@) % 2) + 
CASE WHEN @ > 4 AND @ % 2 <> 0 THEN 1 ELSE 0 END ROWS ONLY

--

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

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com