T-SQL Query | [ The Median Puzzle ]

Best post on Median – http://sqlperformance.com/2012/08/t-sql-queries/median

What is a Median

As per http://en.wikipedia.org/wiki/Median, “The median is the numerical value separating the higher half of a data sample, a population, or a probability distribution, from the lower half.”

1. The puzzle is very cool.
2. You have to find out the median from the integer input data.
3. Please check out the sample input and expected output for details.

Sample Input

 Value 1 3 7 17 23 42

Expected Output

 Median 12

Rules/Restrictions

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

Script

Use the below script to generate the source table and fill them up with the sample data.

 — ``` CREATE TABLE Median ( Value INT ) GO INSERT INTO Median VALUES (1),(3),(7),(17),(23),(42) ``` —

Update May 10 | Solutions – Pawan Kumar Khowal

 ``` -- /************ SOLUTION 0 - Pawan Kumar Khowal *********************/ ;WITH CTE AS ( SELECT Value,ROW_NUMBER() OVER (ORDER BY Value) rnk FROM Median ) SELECT SUM(Value)/2.0 Median FROM CTE WHERE rnk BETWEEN (SELECT (COUNT(*)+1)/2 FROM Median) AND (SELECT (COUNT(*)+2)/2 FROM Median) /************ SOLUTION 1 - Using Analytical Function *********************/ SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Value) OVER () Median FROM dbo.Median /************ SOLUTION 2 - Aaron Bertrand *********************/ ;WITH CTE AS ( SELECT Value, COUNT(*) OVER () rnk1 ,ROW_NUMBER() OVER (ORDER BY value DESC) rnk2 FROM Median ) SELECT SUM(Value) / 2. Median FROM CTE WHERE rnk2 BETWEEN (rnk1 + 1) / 2 AND (rnk1+2) / 2 /************ SOLUTION 3 - Aaron Bertrand *********************/ ;WITH CTE AS ( SELECT Value,ROW_NUMBER() OVER (ORDER BY value) rnk1 ,ROW_NUMBER() OVER (ORDER BY value DESC) rnk2 FROM Median ) SELECT SUM(Value) / 2. Median FROM CTE WHERE rnk1 >= rnk2 - 1 AND rnk1 <= rnk2 + 1 /************ SOLUTION 4 - Itzik Ben-Gan *********************/ SELECT AVG(1.0 * value) Median FROM ( SELECT value FROM dbo.Median ORDER BY value OFFSET ((SELECT COUNT(*) FROM Median) - 1) / 2 ROWS FETCH NEXT 1 + (1 - (SELECT COUNT(*) FROM Median) % 2) ROWS ONLY ) AS x; -- ```

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

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal