/*This script will calculate the median off of the Freight column in the Orders

table in the Northwind database. Interesting because it is faster than the

set based solutions that use a cross join to solve for the median. Not real

useful though. Hopefully the next version of SQL Server will come

with a median function built in.

By C. Eric Cashon

*/

USE Northwind

DECLARE @RowCount1 int

DECLARE @RowCount2 int

DECLARE @RowCountStr varchar(10)

DECLARE @Remainder int

DECLARE @SQLstring1 varchar(8000)

SET @RowCount1 = (SELECT COUNT(*) FROM Orders)

SET @RowCount2 = @RowCount1 / 2 + 1

SET @Remainder = @RowCount1%2

SET @RowCountStr = CAST(@RowCount2 AS varchar(10))

IF(@Remainder <> 0)

BEGIN

SET @SQLstring1 = 'SELECT MAX(T3.Freight) Median FROM

(SELECT TOP 2 T2.Freight FROM

(SELECT TOP ' + @RowCountStr + ' T1.Freight FROM Orders as T1 ORDER BY Freight ASC)

AS T2 ORDER BY Freight DESC) AS T3'

END

ELSE

BEGIN

SET @SQLstring1 = 'SELECT SUM(T3.Freight)/2 Median FROM

(SELECT TOP 2 T2.Freight FROM

(SELECT TOP ' + @RowCountStr + ' T1.Freight FROM Orders as T1 ORDER BY Freight ASC)

AS T2 ORDER BY Freight DESC) AS T3'

END

PRINT @SQLstring1

EXECUTE(@SQLstring1)