This is taken from Joe Celko's "SQL FOR SMARTIES" book, second edition: 23.2.8 Celko's Third Median ... lots of stuff explaining things .. SELECT AVG(DISTINCT weight) FROM (SELECT P1.weight FROM Parts AS P1, Parts AS P2 GROUP BY P1.pno, P1.weight HAVING SUM(CASE WHEN P2.weight = P1.weight THEN 1 ELSE 0 END) >= ABS(SUM(CASE WHEN P2.weight P1.weight THEN -1 ELSE 0 END))) AS Partitions; If you prefer to use functions instead of a CASE expression, then use this version of the query: SELECT AVG(DISTINCT weight) FROM (SELECT P1.weight FROM Parts AS P1, Parts AS P2 GROUP BY P1.pno, P1.weight HAVING SUM(ABS(1 - SIGN(P1.weight - P2.weight)) >= ABS(SUM(SIGN (P1.weight - P2.weight))) AS Partitions;
|