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

 Value 1

Expected output – 1

 Middle4Records 1

Sample Input – 2

 Value 1 3 7 17 23

Expected output – 2

 Middle4Records 3 7 17 23

Sample Input – 3

 Value 1 3 7 17 23 42 20 23 21 31 45 39

Expected output – 3

 Middle4Records 20 21 23 23

Rules/Restrictions

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

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